重新整理AUTO_INCREMENT字段

节选择《Netkiller MySQL 手札》

13.9. 重新整理AUTO_INCREMENT字段

AUTO_INCREMENT 并非按照我们意愿,顺序排列,经常会跳过一些数字,例如当插入失败的时候,再次插入会使用新的值。有时会造成浪费,我们可以使用下面SQL重新编排AUTO_INCREMENT序列。

SET @newid=0;
UPDATE mytable SET id = (SELECT @newid:=@newid+ 1);		

使用max()查看最大值,然后使用 alter修改起始位置。

select max(id) from mytable;
ALTER TABLE mytable AUTO_INCREMENT = 1000;		

注意外键,需要 ON UPDATE CASCADE 支持,否则无法更新。CONSTRAINT `FK_group_has_contact_contact` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,

CREATE TABLE `contact` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '唯一ID',
	`name` VARCHAR(50) NOT NULL COMMENT '姓名',
	`mobile` VARBINARY(32) NULL DEFAULT NULL COMMENT '手机号码',
	`email` VARBINARY(50) NULL DEFAULT NULL COMMENT '电子邮件',
	`mobile_digest` VARCHAR(32) NULL DEFAULT NULL COMMENT '摘要',
	`email_digest` VARCHAR(32) NULL DEFAULT NULL COMMENT '邮件摘要',
	`birthday` DATE NULL DEFAULT NULL COMMENT '生日',
	`description` VARCHAR(255) NULL DEFAULT NULL COMMENT '备注描述',
	`status` ENUM('Subscription','Unsubscribe') NOT NULL DEFAULT 'Subscription' COMMENT '订阅状态',
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `digest` (`mobile_digest`, `email_digest`)
)
COMMENT='会员手机短信与电子邮件映射表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=43642;

CREATE TABLE `group` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`description` VARCHAR(512) NOT NULL,
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `name` (`name`)
)
COMMENT='短信分组'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=8;

CREATE TABLE `group_has_contact` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`group_id` INT(10) UNSIGNED NOT NULL,
	`contact_id` INT(10) UNSIGNED NOT NULL,
	`ctime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `group_contact` (`group_id`, `contact_id`),
	INDEX `FK_group_has_contact_contact` (`contact_id`),
	CONSTRAINT `FK_group_has_contact_contact` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `FK_group_has_contact_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COMMENT='N:M'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=55764;

原文发布于微信公众号 - Netkiller(netkiller-ebook)

原文发表时间:2017-06-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏腾讯云TStack专栏

Zabbix 分区优化

目前 zabbix 的数据主要存储在 history 和 trends 的2个表中,随着时间的推移,这两个表变得非常大,性能会非常差,影响监控的使用。对 MyS...

1.1K0
来自专栏我的博客

触发器统计记录行数

a表保存记录,b表保存a表当中指定cid的总行数 CREATE TABLE `a` ( `id` int(11) unsigned NOT NULL AUT...

2967
来自专栏从零开始的linux

mysql基础语法 表

#if语句,防止报错 mysql> drop database if exists `HA-test`; Query OK, 0 rows affected, ...

3205
来自专栏kwcode

SQL 存储过程分页

CREATE PROC p_Team_GetTemaList @pageindex INT , @pagesize INT , @key...

2718
来自专栏康怀帅的专栏

MySQL 查找数据 SELECT

SELECT 字段1,字段2 FROM 表名; SELECT 表名.字段名 FROM 表名; 别名 SELECT 字段 AS 别名 FROM 表名; 偏移量 S...

2696
来自专栏听雨堂

使用子查询时应当注意的

在一个查询中: UPDATE a SET a.scts = b.v1, a.YCYL = b.v2, a.YCSL = b.v3 FROM kfdbsyy a,...

18610
来自专栏听雨堂

一次数据库的整理的sql语句

//查询以井结束的记录 SELECT f_wellnumber, SUBSTRING(f_wellnumber, 1, LEN(f_wellnumber) - ...

1779
来自专栏数据分析

[数据库基础]——快速浏览日期时间转换

阅读导航 数据库日期和时间类型 相互转换     time ⇌ date time ⇌ smalldatetime time ⇌ datetime tim...

2666
来自专栏IT开发技术与工作效率

MySQL #{驼峰字段} for MyBatis

692
来自专栏文渊之博

mysql replace into 的使用情况

 发现,auto_increment并没有+1,而是针对原来的那一条id=4的记录进行了update,因为没有指定其他列(v,extra)的值,所以,updat...

583

扫码关注云+社区