mysql的where可以搭配insert语句(MySQL数据库insert语句7种写法)
很多开发人员工作了几年之后,都会自嘲,自己啥技术都没学到,就会CRUD,可是我要说的是,CRUD你真的都会吗,你在MySQL数据库中,会几种insert语句写法呢。在这里我会7种写法,下面我就来给大家分别介绍一下。
创建测试表
在介绍Insert语句之前,先创建一张测试表
mysql> show create table t_test_2\G;
*************************** 1. row ***************************
Table: t_test_2
Create Table: CREATE TABLE `t_test_2` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL DEFAULT '',
`name2` char(15) NOT NULL DEFAULT '',
`status` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_t_test_1_name2` (`name2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
不指定插入列写法,这个写法大家很非常熟悉。
mysql> select * from t_test_2;
Empty set (0.00 sec)
mysql> insert into t_test_2 values(20,'name20','tname20',0);
Query OK, 1 row affected (0.53 sec)
mysql> select * from t_test_2;
---- -------- --------- --------
| id | name | name2 | status |
---- -------- --------- --------
| 20 | name20 | tname20 | 0 |
---- -------- --------- --------
1 row in set (0.00 sec)
可是在这里,我要告诉大家的是,不实际写代码的时候,不建议大家使用这种不指定插入列的insert写法,为什么呢,举个例子,当在表上添加一个新字段之后,你的insert语句就会报错,给大家举个例子
mysql> insert into t_test_3 values(20,'name20','tname20',0);
Query OK, 1 row affected (0.43 sec)
mysql> select * from t_test_3;
---- -------- --------- --------
| id | name | name2 | status |
---- -------- --------- --------
| 20 | name20 | tname20 | 0 |
---- -------- --------- --------
1 row in set (0.00 sec)
mysql> alter table t_test_3 add name3 char(10);
Query OK, 0 rows affected (2.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t_test_3 values(21,'name21','tname21',0);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
看到了吧,执行insert语句报错了,如果你生产上代码也是这样,就是一个故障了。
第二种insert写法指定插入列insert语句写法,写法如下所示
mysql> insert into t_test_2(id,name,name2,status) values(21,'name21','tname21',0);
Query OK, 1 row affected (0.39 sec)
mysql> select * from t_test_2;
---- -------- --------- --------
| id | name | name2 | status |
---- -------- --------- --------
| 20 | name20 | tname20 | 0 |
| 21 | name21 | tname21 | 0 |
---- -------- --------- --------
2 rows in set (0.00 sec)
一次插入多行记录,写法如下所示,在这里,一次插入2条记录,当有批量插入的业务情景,这个insert写法就很方便了。
mysql> insert into t_test_2 values(22,'name22','tname22',0),(23,'name23','tname23',0);
Query OK, 2 rows affected (0.37 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t_test_2;
---- -------- --------- --------
| id | name | name2 | status |
---- -------- --------- --------
| 20 | name20 | tname20 | 0 |
| 21 | name21 | tname21 | 0 |
| 22 | name22 | tname22 | 0 |
| 23 | name23 | tname23 | 0 |
---- -------- --------- --------
4 rows in set (0.01 sec)
带select查询的insert语句,这种写法,可以很方便的在线备份一张表的记录
mysql> insert into t_test_2 select * from t_test_1;
Query OK, 10 rows affected (0.33 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from t_test_2;
---- -------- --------- --------
| id | name | name2 | status |
---- -------- --------- --------
| 1 | name1 | 1001 | 0 |
| 2 | name1 | 1002 | 1 |
| 3 | name1 | 1003 | 1 |
| 4 | name1 | 1004 | 0 |
| 5 | name1 | 1005 | 1 |
| 6 | name1 | 1006 | 0 |
| 7 | name1 | 1007 | 2 |
| 8 | name1 | 1008 | 0 |
| 9 | name1 | 1009 | 1 |
| 10 | name10 | 1001 | 0 |
| 20 | name20 | tname20 | 0 |
| 21 | name21 | tname21 | 0 |
| 22 | name22 | tname22 | 0 |
| 23 | name23 | tname23 | 0 |
---- -------- --------- --------
14 rows in set (0.01 sec)
当用replace into向业务表中插入数据时,会依据主键或者唯一索引进行判断,是否存在冲突,如果存在,就会先删除冲突行,然后插入新的记录。如果不冲突,则直接将记录插入到业务表。需要注意的是,想要replace into,用户对表必须有insert,delete权限,在管理严格线上数据库,会禁止普通应用用户拥有delete权限。
举个例子,当你插入的数据发生主键冲突时,就可以使用replace into来解决了。下面来举个例子
mysql> select * from t_test_2 where id=1;
---- ------- ------- --------
| id | name | name2 | status |
---- ------- ------- --------
| 1 | name1 | 1001 | 0 |
---- ------- ------- --------
1 row in set (0.00 sec)
mysql> replace into t_test_2 (id,name,name2,status) values(1,'testea','testea',1);
Query OK, 2 rows affected (0.16 sec)
mysql> select * from t_test_2 where id=1;
---- -------- -------- --------
| id | name | name2 | status |
---- -------- -------- --------
| 1 | testea | testea | 1 |
---- -------- -------- --------
1 row in set (0.00 sec)
当用insert ignore向业务表插入数据时,会依据主键或者唯一索引进行判断,是否存在冲突,就跳过要插入的数据,如果不冲突,则直接将记录插入到业务表。
举一个业务场景,当你向表里插入记录,可是有些数据可能会与表里的记录发生主键冲突,而发生冲突的记录,你不想导入,这个时候,insert ignore就能帮上大忙了。举个实际例子
mysql> insert ignore into t_test_2 (id,name,name2,status) values(1,'testea','testea',1),(30,'teste30','teste30',1);
Query OK, 1 row affected, 1 warning (0.34 sec)
Records: 2 Duplicates: 1 Warnings: 1
mysql> select * from t_test_2 where id in (1,30);
---- --------- --------- --------
| id | name | name2 | status |
---- --------- --------- --------
| 1 | testea | testea | 1 |
| 30 | teste30 | teste30 | 1 |
---- --------- --------- --------
2 rows in set (0.01 sec)
当用insert on duplicate key update向业务表插入数据时,会依据主键或者唯一索引进行判断,是否存在冲突,则会更新指定字段,如果不冲突,则直接将记录插入到业务表。
举个例子
mysql> select * from t_test_2 where id in (1,31);
---- -------- -------- --------
| id | name | name2 | status |
---- -------- -------- --------
| 1 | testea | testea | 1 |
---- -------- -------- --------
1 row in set (0.00 sec)
mysql> insert into t_test_2 (id,name,name2,status) values(1,'testea','testea',1) on duplicate key update status=4;
Query OK, 2 rows affected (0.32 sec)
mysql> insert into t_test_2 (id,name,name2,status) values(31,'testea31','testea31',1) on duplicate key update status=4;
Query OK, 1 row affected (0.14 sec)
mysql> select * from t_test_2 where id in (1,31);
---- ---------- ---------- --------
| id | name | name2 | status |
---- ---------- ---------- --------
| 1 | testea | testea | 4 |
| 31 | testea31 | testea31 | 1 |
---- ---------- ---------- --------
2 rows in set (0.00 sec)
1.如果您喜欢这篇文章,请点赞 转发。
2.如果您特别喜欢,请加关注。
,免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com