mysql 重置主键值

根

qqmusic.zip

据目前水平，思来想去解决主键值一直无限增长到最大同时保留原有数据的最优方案就是：

1. 备份原表

2. truncate原表

3. 从备份表把除了主键之外的数据插入回来

4. 删除备份表

5. 结束

sql语句如下：

# 删除可能存在的备份表
DROP TABLE IF EXISTS TableA_backup;
# 创建备份表
CREATE TABLE TableA_backup AS SELECT * FROM TableA;
# 锁表防止数据写入
LOCK TABLES TableA WRITE TableA_backup READ;
# 清空原表
TRUNCATE TABLE TableA;
# 获取除了id之外的键名并且组合，可以换成  COLUMN_KEY != 'PRI'
SET @columns = (SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableA' AND COLUMN_NAME != 'PRI');
# 生成执行语句执行
SET @sql = CONCAT('INSERT INTO TableA (', @columns, ') SELECT ', @columns, ' FROM TableA_backup');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# 解锁表
UNLOCK TABLES;
# 删除备份表
DROP TABLE IF EXISTS TableA_backup;

shell自动执行脚本：

#!/bin/bash
path0="/opt/resetId"
logs="${path0}/reset.log"
mysqlPasswd=""
mysqlUser=""
mysqlDB=""
tables="TableA TableB TableC" # 换成你需要重置id的表名称，确认表id增长快且无其他地方引用
log() {
    echo "$(date +'%Y-%m-%d %H:%M:%S') $1" >> "$logs"
}
log " "
log "shell start "
for table_name in $tables;do
log " "
    log "action $table_name"
    min_id=$(mysql -u${mysqlUser} -p${mysqlPasswd} ${mysqlDB} -e "select min(id) from ${table_name}\G" | grep min | awk '{print $2}')
    log "${tables_name} before reset min_id ${min_id}"
    if [ "$min_id" != "1" ];then
        mysql -u${mysqlUser} -p${mysqlPasswd} ${mysqlDB} -e "DROP TABLE IF EXISTS ${table_name}_backup;\
        CREATE TABLE ${table_name}_backup AS SELECT * FROM ${table_name};\
        LOCK TABLES ${table_name} WRITE, ${table_name}_backup READ;\
        TRUNCATE TABLE ${table_name};\
        SET @columns = (SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '${table_name}' AND COLUMN_NAME != 'id');\
        SET @sql = CONCAT('INSERT INTO ${table_name} (', @columns, ') SELECT ', @columns, ' FROM ${table_name}_backup');\
        PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;\
        UNLOCK TABLES;DROP TABLE IF EXISTS ${table_name}_backup;"
    fi
    min_id=$(mysql -u${mysqlUser} -p${mysqlPasswd} ${mysqlDB} -e "select min(id) from ${table_name}\G" | grep min | awk '{print $2}')
    log "${tables_name} after reset min_id ${min_id}"
done
log "end"
log " "

还有一个办法，就是删除主键，然后再新建一个主键：

alter table TableA drop id;
alter table TableA add id int(11) not null auto_increment first, add primary key (id);

以400w条数据测，上面那个备份清空再导入回来的大概 1分20多秒 ， 下面直接删了主键再新建的因为每次操作的时候mysql会到临时表操作，花了2分钟，不过这个方式不用考虑中途会有数据插入的情况，语句简单倒也省事。