锐客网

  1. 首页 > 睿知 > it技术 > >

怎么增加mysql的列 mysql怎么增加一列( 三 )

IT技术数据


[root@localhost][test1]insert into t(name) values ('test');
QueryOK, 1 row affected (0.01 sec)
[root@localhost][test1]insert into t(name) values ('test');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
可以看到 ， 第一个插入没问题，因为自增列的值为2147483647，这是达到了上限，还没有超过，第二行数据插入时，则报出主键重复，在达到上限后，无法再分配新的更大的自增值，也没有从1开始从头分配，在这里表的auto_increment值会一直是2147483647 。
对于写入量大，且经常删除数据的表，自增id设为int类型还是偏小的 ， 所以我们为了避免出现自增id涨满的情况，这边统一建议自增id的类型设为unsigned bingint，这样基本可以保障表的自增id是永远够用的 。
这里内容比较多，innodb是索引组织表，所以涉及到索引的知识，但这不是本文的重点，我们快速回顾索引知识：
1.Innodb索引分为主键跟辅助索引，主键即全表，辅助索引叶子节点保存主键的值，而主键的叶子节点保存数据行，中间节点存着叶子节点的路由值 。
2.Innodb存储数据（索引）的单位是页，这里默认是16K，这也意味着，数据本身越?。?一个页中能存数据的量越多，而检索效率不仅仅由索引的层数来决定，更是由一次能够缓存的数据量来定，也就是说数据本身越小，则一次IO能够提取到缓冲区的数据越多（OS每次IO的量是固定的4K） ， 查询的效率越好 。
其实能够理解索引的结构及索引写入插入、更新的原理，则自然就明白为何建议使用自增id 。这里我直接列出使用自增id 当主键的好处吧：
1.顺序写入，避免了叶的分裂，数据写入效率好
2.缩小了表的体积 ， 特别是相比于UUID当主键，甚至组合字段当主键时 ， 效果更明显
3.查询效率好，原因就是我上面说到索引知识的第二点 。
4.某些情况下，我们可以利用自增id来统计大表的大致行数 。
5.在数据归档or垃圾数据清理时，也可方便的利用这个id去操作，效率高 。
容易出现不连续的id
有的同志会发现，自己的表中id值存在空洞，如类似于1、2、3、8、9、10这样，有的适合有想依赖于自增id的连续性来实现业务逻辑，所以会想方设法去修改id让其变的连续，其实，这是没有必要的，这一块的业务逻辑交由MySQL实现是很不理智的，表的记录小还好 ， 要是表的数据量很大 ， 修改起来就糟糕了 。那么，为什么自增id会容易出现空洞呢？
自增id的修改机制如下：
在MySQL里面，如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：
1. 如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的
AUTO_INCREMENT值填到自增字段；
2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值 。
根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同 。假设，某次要插入的值是X，当前的自增值是Y 。
1. 如果XY ， 那么这个表的自增值不变；
2. 如果X≥Y，就需要把当前自增值修改为 新的自增值。
新的自增值生成算法是：从auto_increment_offset开始 ， 以auto_increment_increment为步长 ， 持续叠加，直到找到第一个大于X的值 ， 作为新的自增值 。
Insert、update、delete操作会让id不连续 。
Delete、update：删除中间数据，会造成空动，而修改自增id值，也会造成空洞（这个很少） 。
Insert：插入报错（唯一键冲突与事务回滚），会造成空洞，因为这时候自增id已经分配出去了，新的自增值已经生成，如下面例子：

推荐阅读


上一篇：抖音上的拍摄用的什么软件的简单介绍

下一篇：暗黑动作解谜游戏，暗黑系解谜游戏