INSERT INTO
- insert into表示插入数据，数据库会检查主键（PrimaryKey），如果出现重复会报错；除了这个之外还有一些配合的参数。
语法如下：INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE][INTO] tbl_name[PARTITION (partition_name [, partition_name] ...)][(col_name [, col_name] ...)]{SELECT ... | TABLE table_name}[ON DUPLICATE KEY UPDATE assignment_list]
- DELAYED：
是立刻返回一个标识，告诉上层程序，数据已经插入了，当表没有被其它线程使用时，此行被插入，真实插入时间就不可控了。所以这样的写法对数据的安全性是没有保障的。
延迟插入和替换在MySQL 5.6中是不推荐的。在MySQL 5.7，MySQL 8.0中，不支持延迟。服务器识别但忽略DELAYED关键字，将插入处理为非延迟插入，并生成er_warn_legacy_syntax_convert警告(“不再支持延迟插入”)。语句被转换为INSERT”)。DELAYED关键字计划在未来的版本中删除。 - LOW_PRIORITY：
插入的执行将被延迟，直到没有其他客户端从表中读取数据。这包括在现有客户端正在读取时以及INSERT LOW_PRIORITY语句正在等待时开始读取的其他客户端。因此，对于发出INSERT LOW_PRIORITY语句的客户机，可能要等待很长时间。
LOW_PRIORITY只影响只使用表级锁的存储引擎(如MyISAM、内存和合并)。
LOW_PRIORITY通常不应该用于MyISAM表，因为这样做会禁用并发插入 - HIGH_PRIORITY:
如果指定了HIGH_PRIORITY，同时服务器采用–low-priority-updates选项启动，则HIGH_PRIORITY将覆盖–low-priority-updates选项。这么做还会导致同时进行的插入被取消。
【 low_priority_updates：如果设置为1，所有插入、更新、删除和锁表写语句都将等待，直到受影响的表上没有未决的选择或锁表读取。使用{INSERT | REPLACE | DELETE | UPDATE} LOW_PRIORITY…仅降低一个查询的优先级。这个变量只影响只使用表级锁的存储引擎(如MyISAM、MEMORY和MERGE)MySQL的merge引擎类型允许把许多结构相同的表合并为一个表,Merge表有点类似于视图。】 - IGNORE：
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据，主键和唯一键为基准； ```sql mysql> select * from user; +——+————-+——-+———+——————————-+—————-+ | id | user_id | age | name | login_time | address | +——+————-+——-+———+——————————-+—————-+ | 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 | +——+————-+——-+———+——————————-+—————-+ 1 row in set (0.00 sec)
mysql> insert ignore into user (id, user_id, age, name, address) values (1, 111, 18, ‘Fcc’, ‘北京市’); Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> show warnings; +————-+———+————————————————————————-+ | Level | Code | Message | +————-+———+————————————————————————-+ | Warning | 1364 | Field ‘login_time’ doesn’t have a default value | | Warning | 1062 | Duplicate entry ‘1’ for key ‘PRIMARY’ | +————-+———+————————————————————————-+ 2 rows in set (0.00 sec)
mysql> select * from user; +——+————-+——-+———+——————————-+—————-+ | id | user_id | age | name | login_time | address | +——+————-+——-+———+——————————-+—————-+ | 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 | +——+————-+——-+———+——————————-+—————-+ 1 row in set (0.00 sec)
mysql>
<a name="t1299"></a># INSERT INTO SELECT把一张表的字段数据导入到另一张表中，执行语句会把整个数据会打包成一个事务执行。<br />创建复制表user1，执行INSERT INTO SELECT语句<br />```sqlmysql> insert into user1 select * from user;Query OK, 2 rows affected (0.02 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from user1;+----+---------+-----+------+---------------------+-----------+| id | user_id | age | name | login_time | address |+----+---------+-----+------+---------------------+-----------+| 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 || 2 | 122 | 18 | wkd | 2020-08-08 22:48:23 | 南京市 |+----+---------+-----+------+---------------------+-----------+2 rows in set (0.00 sec)
查看binlog的日志
mysqlbinlog --no-defaults --database=test --start-datetime="2020-08-09 10:22:00" --stop-datetime="2020-08-09 23:21:00" ..\data\binlog.000021 | more
注意：当从同一个表中选择和插入时，MySQL创建一个内部临时表来保存SELECT中的行，然后将这些行插入到目标表中。但是，不能使用INSERT INTO t…选择……当t是临时表时，从t开始，因为临时表不能在同一语句中被引用两次。
REPLACE INTO
replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中，
1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
2. 否则，直接插入新数据。
3. REPLACE，必须同时拥有表的INSERT，UPDATE，DELETE权限。
语法：
REPLACE [LOW_PRIORITY | DELAYED][INTO] tbl_name[PARTITION (partition_name [, partition_name] ...)][(col_name [, col_name] ...)]{ {VALUES | VALUE} (value_list) [, (value_list)] ...|VALUES row_constructor_list}REPLACE [LOW_PRIORITY | DELAYED][INTO] tbl_name[PARTITION (partition_name [, partition_name] ...)]SET assignment_listREPLACE [LOW_PRIORITY | DELAYED][INTO] tbl_name[PARTITION (partition_name [, partition_name] ...)][(col_name [, col_name] ...)]{SELECT ... | TABLE table_name}
注意：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
1.主键和唯一键同时存在的时候语句不一样。
主键：是进行update操作。
mysql> select * from user1;+----+---------+-----+------+---------------------+-----------+| id | user_id | age | name | login_time | address |+----+---------+-----+------+---------------------+-----------+| 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 || 2 | 122 | 18 | wkd | 2020-08-08 22:48:23 | 南京市 || 3 | 131 | 17 | Fcc | 0000-00-00 00:00:00 | 苏州市 |+----+---------+-----+------+---------------------+-----------+3 rows in set (0.00 sec)mysql> replace into user1 (id, user_id , age, name, login_time, address) values (3, 133, 17, 'Fcc', 0, '上海市');Query OK, 2 rows affected (0.01 sec)mysql> select * from user1;+----+---------+-----+------+---------------------+-----------+| id | user_id | age | name | login_time | address |+----+---------+-----+------+---------------------+-----------+| 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 || 2 | 122 | 18 | wkd | 2020-08-08 22:48:23 | 南京市 || 3 | 133 | 17 | Fcc | 0000-00-00 00:00:00 | 上海市 |+----+---------+-----+------+---------------------+-----------+3 rows in set (0.00 sec)
查看binlog
mysqlbinlog --no-defaults --database=test --start-datetime="2020-08-09 10:22:00" --stop-datetime="2020-08-09 23:21:00" ..\data\binlog.000021 | more
主键+唯一键：delete+insert操作。
mysql> select * from user1;+----+---------+-----+------+---------------------+-----------+| id | user_id | age | name | login_time | address |+----+---------+-----+------+---------------------+-----------+| 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 || 2 | 122 | 18 | wkd | 2020-08-08 22:48:23 | 南京市 || 3 | 133 | 18 | Fcc | 0000-00-00 00:00:00 | 上海市 |+----+---------+-----+------+---------------------+-----------+3 rows in set (0.00 sec)mysql> desc user1;+------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || user_id | varchar(50) | NO | UNI | | || age | int(11) | NO | MUL | NULL | || name | varchar(255) | NO | | NULL | || login_time | datetime | NO | MUL | NULL | || address | varchar(255) | NO | MUL | NULL | |+------------+--------------+------+-----+---------+----------------+6 rows in set (0.01 sec)mysql> replace into user1 (id, user_id , age, name, login_time, address) values (3, 133, 19, 'Fcant', 0, '苏州市');Query OK, 2 rows affected (0.02 sec)
查看binlog后发现
mysqlbinlog --no-defaults --database=test --start-datetime="2020-08-09 10:22:00" --stop-datetime="2020-08-09 23:21:00" ..\data\binlog.000021 | more
2. 但对于同一个数据所有行都一样的时候replace into就不会进行更新操作。
mysqlbinlog --no-defaults --database=test --start-datetime="2020-08-09 10:22:00" --stop-datetime="2020-08-09 23:21:00" ..\data\binlog.000021 | more
INSERT INTO ON DUMPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE语句，并且要插入的行将导致惟一索引或主键中出现重复值，则会对旧行进行更新。但主键和唯一键同时存在的时候，选择主键。
实际验证流程如下：
主键不存在，执行INSERT
mysql> select * from user1;+----+---------+-----+-------+---------------------+-----------+| id | user_id | age | name | login_time | address |+----+---------+-----+-------+---------------------+-----------+| 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 || 2 | 122 | 18 | wkd | 2020-08-08 22:48:23 | 南京市 || 3 | 133 | 19 | Fcant | 0000-00-00 00:00:00 | 上海市 |+----+---------+-----+-------+---------------------+-----------+3 rows in set (0.00 sec)mysql> insert into user1 (id, user_id , age, name, login_time, address) values (4, 144, 14, 'Fcc4', 0, '上海市') on duplicate key update address = '上海市';Query OK, 1 row affected (0.01 sec)mysql> select * from user1;+----+---------+-----+-------+---------------------+-----------+| id | user_id | age | name | login_time | address |+----+---------+-----+-------+---------------------+-----------+| 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 || 2 | 122 | 18 | wkd | 2020-08-08 22:48:23 | 南京市 || 3 | 133 | 19 | Fcant | 0000-00-00 00:00:00 | 上海市 || 4 | 144 | 14 | Fcc4 | 0000-00-00 00:00:00 | 上海市 |+----+---------+-----+-------+---------------------+-----------+4 rows in set (0.00 sec)
主键存在，执行UPDATE
mysql> select * from user1;+----+---------+-----+-------+---------------------+-----------+| id | user_id | age | name | login_time | address |+----+---------+-----+-------+---------------------+-----------+| 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 || 2 | 122 | 18 | wkd | 2020-08-08 22:48:23 | 南京市 || 3 | 133 | 19 | Fcant | 0000-00-00 00:00:00 | 苏州市 |+----+---------+-----+-------+---------------------+-----------+3 rows in set (0.00 sec)mysql> insert into user1 (id, user_id , age, name, login_time, address) values (3, 133, 18, 'Fcc', 0, '上海市') on duplicate key update address = '上海市';Query OK, 2 rows affected (0.01 sec)mysql> select * from user1;+----+---------+-----+-------+---------------------+-----------+| id | user_id | age | name | login_time | address |+----+---------+-----+-------+---------------------+-----------+| 1 | 111 | 18 | Fc | 2020-07-23 22:16:57 | 北京市 || 2 | 122 | 18 | wkd | 2020-08-08 22:48:23 | 南京市 || 3 | 133 | 19 | Fcant | 0000-00-00 00:00:00 | 上海市 |+----+---------+-----+-------+---------------------+-----------+3 rows in set (0.00 sec)
总结
- 在实际sysbench压测中，硬件配置比较好的情况，混合&插入模式下 MySQL的单台TPS能到 1w~6w的性能。insert也是有极限的，超过这个范围的时候，会存在延迟等性能瓶颈。
2. REPLACE INTO性能中 delete insert索引页分裂可能非常严重。需要注意
3. INSERT ON DUPLICATE KEY UPDATE如果一个表定义有多个唯一键或 主键同时存在时，是不安全的，这会引发操作错误，导致数据处理错误。
4. INSERT SELECT是 表之间迁移数据的很好的方式，但需要用带索引的字段进行条件和排序限制。除此之外数据量多的时候，可以理解成一个大事务。
若有收获，就点个赞吧
0 人点赞
