暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 分析MySQL 中 replace into

分析MySQL 中 replace into

和我一起的9分钟 2019-09-10
356



在网上搜索到的各种博客都显示replace into 使用过程中存在坑，为了验证，直接实验解决：

  1. 搭建一个主从环境

操作系统主机IP功能
centos 710.211.55.9Master/Slave
centos 710.211.55.10Master/Slave

配置文件：先10.211.55.10为Master，10.211.55.9为Slave 实验过程中会发生角色切换

## 10.211.55.10的 my.cnf 文件
[client]
port=3306
socket	= mysql/data/3306/mysql.sock

[mysql]
no-beep
prompt="\u@itpux \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8

[mysqld]
########basic settings########
#skip-grant-tables
server-id=55103306
port=3306
user = mysql
#bind_address= 10.211.55.10
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket	= mysql/data/3306/mysql.sock
pid-file=/mysql/data/3306/mysql.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M

back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000

########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/3306/itpuxdb-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/itpuxdb-query.err
long_query_time=10
log-error=/mysql/log/3306/itpuxdb-error.err

log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 10
min_examined_row_limit = 100
#log_bin = "/log/bin_log/binlog"

########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors

###### master add parameter########
log_bin = mysql/log/3306/binlog/itpuxdb-binlog
log_bin = on
log_bin_index = mysql/log/3306/binlog/itpuxdb-binlog.index
binlog_format = row
binlog_rows_query_log_events = on

#### master modify parameter######
skip_name_resolve = on
innodb_support_xa = 1
binlog_cache_size = 1M
max_binlog_size = 256M
log_bin_trust_function_creators = 1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
transaction-isolation = read-committed

gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery = 1

###### slave parameter#########
#relay_log = mysql/log/3306/relaylog/itpuxdb-relay.log
#read_only = 1
#slave-parallel-type = LOGICAL_CLOCK
#slave-parallel-workers = 4
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#relay_log_recovery = 1
#slave_skip_errors = ddl_exist_errors
#slave_preserve_commit_order = 1



########innodb settings########
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G

innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G

innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

## 10.211.55.9的 my.cnf 文件
[client]
port=3306
socket	= mysql/data/3306/mysql.sock

[mysql]
no-beep
prompt="\u@itpux \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8


[mysqld]
########basic settings########
server-id=55093306
port=3306
user = mysql
#bind_address= 10.211.55.9
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket	= mysql/data/3306/mysql.sock
pid-file=/mysql/data/3306/mysql.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M

back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000

########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/3306/itpuxdb-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/itpuxdb-query.err
long_query_time=10
log-error=/mysql/log/3306/itpuxdb-error.err

log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 10
min_examined_row_limit = 100
#log_bin = "/log/bin_log/binlog"

########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors

###### master add parameter########
log_bin = mysql/log/3306/binlog/itpuxdb-binlog
log_bin = on
log_bin_index = mysql/log/3306/binlog/itpuxdb-binlog.index
binlog_format = row
binlog_rows_query_log_events = on


#### master modify parameter######
skip_name_resolve = on
innodb_support_xa = 1
binlog_cache_size = 1M
max_binlog_size = 256M
log_bin_trust_function_creators = 1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
transaction-isolation = read-committed

gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery = 1

###### slave parameter#########
relay_log = mysql/log/3306/relaylog/itpuxdb-relay.log
read_only = 1
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors
slave_preserve_commit_order = 1

########innodb settings########
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G

innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G

innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

开始测试：

  1. 建表

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

test表有一个自增的 id 字段作为主键，字段 col_1 有 UNIQUE KEY 做唯一性约束。

错误场景一实测：想更新某些列的值，但是不需要更新的列值不希望变为NULL 

1. 初始化插入数据 master节点操作：
REPLACE INTO test1 (col_1,col_2,col_3) values('a','a','a');
REPLACE INTO test1 (col_1,col_2,col_3) values('b','b','b');
REPLACE INTO test1 (col_1,col_2,col_3) values('c','c','c');
commit;

show create table test.test1;
| test1 | CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

mysql> select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  2 | b     | b     | b     |
|  3 | c     | c     | c     |
+----+-------+-------+-------+
3 rows in set (0.00 sec)

2. slave 节点查看
show create table test.test1;
| test1 | CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

mysql> select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  2 | b     | b     | b     |
|  3 | c     | c     | c     |
+----+-------+-------+-------+
3 rows in set (0.00 sec)

3. master 节点
mysql> replace into test1(col_1,col_2) values('c','cc');
Query OK, 2 rows affected (0.00 sec)

select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  2 | b     | b     | b     |
|  4 | c     | cc    | NULL  |
+----+-------+-------+-------+
3 rows in set (0.00 sec)

show create table test1;
| test1 | CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

4. slave 节点
select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  2 | b     | b     | b     |
|  4 | c     | cc    | NULL  |
+----+-------+-------+-------+
3 rows in set (0.00 sec)

show create table test1;
| test1 | CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

结论：建议的解决办法：replace into 时将所有列的值都插入进去，不要只插入更新的那一列，就不会存在这个问题。

错误场景二：master和slave的AUTO_INCREMENT值不一样，假设有一天，master 挂了， 由slave 提升为 new master会导致插入错误，主从不一致 

1. 在master上操作
REPLACE INTO test1 (col_1,col_2) values('d','d');
REPLACE INTO test1 (col_1,col_2) values('d','dd');
commit;

select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  2 | b     | b     | b     |
|  4 | c     | cc    | NULL  |
|  6 | d     | dd    | NULL  |
+----+-------+-------+-------+
4 rows in set (0.00 sec)

show create table test1;
| test1 | CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |

2. slave 上查看
select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  2 | b     | b     | b     |
|  4 | c     | cc    | NULL  |
|  6 | d     | dd    | NULL  |
+----+-------+-------+-------+
4 rows in set (0.00 sec)

show create table test.test1;
| test1 | CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

3. 模拟master down机
3.1 master主机
systemctl stop mysql

3.2 slave
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: 10.211.55.10
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: on.000003
          Read_Master_Log_Pos: 3792
               Relay_Log_File: itpuxdb-relay.000005
                Relay_Log_Pos: 3911
        Relay_Master_Log_File: on.000003
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes

3.3 将slave提为master节点
show slave status\G;
vim mysql/data/3306/my.cnf   -- 修改从的配置文件为主的配置文件
systemctl restart mysql -- 重启mysql服务

-- 查看从slave提升为master节点的当前数据
select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  2 | b     | b     | b     |
|  4 | c     | cc    | NULL  |
|  6 | d     | dd    | NULL  |
+----+-------+-------+-------+
4 rows in set (0.00 sec)

show create table test.test1;

| test1 | CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |

模拟向新的master节点插入两条数据：
REPLACE INTO test1 (col_1,col_2) values('d','ddd');
REPLACE INTO test1 (col_1,col_2) values('d','dddd');
REPLACE INTO test1 (col_1,col_2) values('b','bb');

select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  4 | c     | cc    | NULL  |
|  8 | d     | dddd  | NULL  |
|  9 | b     | bb    | NULL  |
+----+-------+-------+-------+
4 rows in set (0.00 sec)

show create table test.test1;

| test1 | CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |


3.4 将原来的master变为slave节点
vim mysql/data/3306/my.cnf  -- 修改配置文件
systemctl start mysql  -- 重启服务

mysql> change master to
    -> master_host = '10.211.55.9',
    -> master_port = 3306,
    -> master_user = 'repuser',
    -> master_password = 'repuser123',
    -> master_auto_position = 1;

select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  4 | c     | cc    | NULL  |
|  8 | d     | dddd  | NULL  |
|  9 | b     | bb    | NULL  |
+----+-------+-------+-------+
4 rows in set (0.00 sec)

show create table test.test1;
| test1 | CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col_1` varchar(100) DEFAULT NULL,
  `col_2` varchar(100) DEFAULT NULL,
  `col_3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |

结论：第一轮切换后 AUTO_INCREMENT不一致，但是主从同步没有任何问题。

再次切换进一步测试 

背景：此时的数据和AUTO_INCREMENT主从是一致的，先插入几条数据让主从的AUTO_INCREMENT发生不一致

master & slave 节点均为：
select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
|  7 | d     | ddddd | NULL  |
|  9 | b     | bbbb  | NULL  |
| 10 | c     | ccc   | NULL  |
+----+-------+-------+-------+

show create table test.test1;
AUTO_INCREMENT=11

REPLACE INTO test1 (col_1,col_2) values('e','e');
REPLACE INTO test1 (col_1,col_2) values('b','b');
REPLACE INTO test1 (col_1,col_2) values('d','d');

master:
select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
| 10 | c     | ccc   | NULL  |
| 11 | e     | e     | NULL  |
| 12 | b     | b     | NULL  |
| 13 | d     | d     | NULL  |
+----+-------+-------+-------+
5 rows in set (0.00 sec)

AUTO_INCREMENT=14

slave:
select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
| 10 | c     | ccc   | NULL  |
| 11 | e     | e     | NULL  |
| 12 | b     | b     | NULL  |
| 13 | d     | d     | NULL  |
+----+-------+-------+-------+
5 rows in set (0.00 sec)

AUTO_INCREMENT=12

1. 模拟主down机
systemctl stop mysql

2. 从变主
修改配置文件，重启mysql服务

3. 查看当前主的信息
select * from test.test1;
+----+-------+-------+-------+
| id | col_1 | col_2 | col_3 |
+----+-------+-------+-------+
|  1 | a     | a     | a     |
| 10 | c     | ccc   | NULL  |
| 11 | e     | e     | NULL  |
| 12 | b     | b     | NULL  |
| 13 | d     | d     | NULL  |
+----+-------+-------+-------+
5 rows in set (0.00 sec)

AUTO_INCREMENT=12

模拟在当前主插入数据：
use test;
REPLACE INTO test1 (col_1,col_2) values('f','f');
ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'
此时发生冲突
AUTO_INCREMENT=13

REPLACE INTO test1 (col_1,col_2) values('f','f');
ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'
此时发生冲突
AUTO_INCREMENT=14

EPLACE INTO test1 (col_1,col_2) values('f','f');
Query OK, 1 row affected (0.01 sec)
show create table test.test1;
AUTO_INCREMENT=15


最终结论：第一种错误场景可以通过在插入时，明确指明所有列的值来避免；第二种replace into确实可能带来主从切换后的主库无法插入，主键冲突的情况。但是主从同步不会发生问题，且多次插入失败后AUTO_INCREMENT会逐渐增加，不冲突后又可以插入数据，所以请谨慎使用replace into


mysql
文章转载自和我一起的9分钟，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论