MySQL自增列(AUTO_INCREMENT)相关知识点计算

发布时间:2019-05-03  栏目:MySQL  评论:0 Comments

 

title: MySQL 小知识点总括
date: 2016-01-09
update time: 2016-05-14
comments: true
category: DB
tags: MySQL, outfile, infile

MySQL的自增列(AUTO_INCREMENT)和此外数据库的自增列比较,有好些个风味和分化点(以致不一样存款和储蓄引擎、分歧版本也有部分见仁见智的表征),让人备感微微稍微复杂。下边大家从局地测试初始,来认知、理解一下那方面的特有知识点:

查询数据库的高低(总大小,数据大小,索引大小)

SELECT ROUND(SUM(data_length+index_length)/1024/1024) AS total_bm,
       ROUND(SUM(data_length)/1024/1024) as data_mb,
       ROUND(SUM(index_length)/1024/1024) as index_mb
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA='peanut_userspace_1' AND TABLE_NAME = 'USER_PAIR_ROUND' ;

 

数据库导出数据到csv文件 (注意 \r\n 和 \n)

SELECT * INTO OUTFILE '/tmp/user_info-table.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM USER_INFO;

# 注意:
      这里可以查询具体的字段,导出到csv文件,就像:
      select id, sex, name into outfile '' ....

 

数据库从csv文件导入数据

LOAD DATA INFILE '/tmp/test.csv' INTO TABLE table_name
    FIELDS TERMINATED BY ',' OPTIONALLY
    ENCLOSED BY '"'
    ESCAPED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES;

# 注意:
      这里可以导入具体的字段到表,就像:
      load data infile '' into table table-name ....  ignore 1 lines (id, sex, name);

自增列持久化难题

MySQL 随机时间

一段时间范围内的随机时间, 结束时间可以为 `now()`
DATE_ADD('2016-05-08 06:00:00',INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP('2016-05-10 20:00:00')-UNIX_TIMESTAMP('2016-05-08 06:00:00')))-1)) SECOND)

DATE_ADD('2016-05-08 06:00:00',INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP(now()')-UNIX_TIMESTAMP('2016-05-08 06:00:00')))-1)) SECOND)

 

举例八个表具备自增列,当前最大自增列值为九,
删除了自增列6、7、八、玖的笔录,重启MySQL服务后,再往表里面插入数据,自增列的值为陆依然十呢? 
即便表的贮存引擎为MyISAM呢,又会是如何处境? 上边实验遇到为MySQL
5.7.二壹

 

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.08 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

 

 

mysql> insert into test(name)

    -> select 'kkk1' from dual union all

    -> select 'kkk2' from dual union all

    -> select 'kkk3' from dual union all

    -> select 'kkk4' from dual union all

    -> select 'kkk5' from dual union all

    -> select 'kkk6' from dual union all

    -> select 'kkk7' from dual union all

    -> select 'kkk8' from dual union all

    -> select 'kkk9' from dual;

Query OK, 9 rows affected (0.01 sec)

Records: 9  Duplicates: 0  Warnings: 0

 

 

mysql> select * from test;

+----+------+

| id | name |

+----+------+

|  1 | kkk1 |

|  2 | kkk2 |

|  3 | kkk3 |

|  4 | kkk4 |

|  5 | kkk5 |

|  6 | kkk6 |

|  7 | kkk7 |

|  8 | kkk8 |

|  9 | kkk9 |

+----+------+

9 rows in set (0.00 sec)

 

mysql> delete from test where id>=6;

Query OK, 4 rows affected (0.00 sec)

 

重启MySQL服务后,然后我们插入一条记下,字段ID会从怎样值开头吧?
如下所示,假使表的囤积引擎为InnoDB,那么插入的数额的自增字段值为⑥.

 

 

图片 1

 

 

接下去,大家创建一个MyISAM类型的测试表。如下所示:

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table test(id int auto_increment  primary key, name varchar(32)) engine=MyISAM;

Query OK, 0 rows affected (0.02 sec)

 

mysql> 

 

insert into test(name)

select 'kkk1' from dual union all

select 'kkk2' from dual union all

select 'kkk3' from dual union all

select 'kkk4' from dual union all

select 'kkk5' from dual union all

select 'kkk6' from dual union all

select 'kkk7' from dual union all

select 'kkk8' from dual union all

select 'kkk9' from dual;

 

 

mysql> delete from test where id>=6;

Query OK, 4 rows affected (0.00 sec)

 

删去了id>=陆的记录后,重启MySQL服务,如下所示,测试结果为id
=十,
那么为何出现差别的几个结果吗?那些是因为InnoDB存款和储蓄引擎中,自增主键未有持久化,而是位于内部存款和储蓄器中,关于自增主键的分红,是由InnoDB数据字典里面一个计数器来调节的,而该计数器只在内部存款和储蓄器中维护,并不会持久化到磁盘中。当数据库重启时,该计数器会通过SELECT
MAX(ID) FROM TEST FOR
UPDATE那样的SQL语句来初步化(差别表对应不一样的SQL语句),
其实这是二个bug来着,
对应的链接地址为:https://bugs.mysql.com/bug.php?id=199,直到MySQL 八.0
,才将自增主键的计数器持久化到redo
log中。每一回计数器产生改换,都会将其写入到redo
log中。如若数据库产生重启,InnoDB会依据redo
log中的计数器音讯来开始化其内部存款和储蓄器值。
而对应与MySIAM存款和储蓄引擎,自增主键的最大值存放在数据文件个中,每便重启MySQL服务都不会影响其值变化。

 

 

图片 2

 

 

 

自增列细节天性

 

1:SQL模式的NO_AUTO_VALUE_ON_ZERO值影响AUTO_INCREMENT列的行为。

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> select @@sql_mode;

+-------------------------------------------------------------------------------------------------------------------------------------------+

| @@sql_mode                                                                                                                                |

+-------------------------------------------------------------------------------------------------------------------------------------------+

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+-------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> insert into test(id, name) value(0, 'kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

+----+-------+

| id | name  |

+----+-------+

|  1 | kerry |

+----+-------+

1 row in set (0.00 sec)

 

mysql> 

 

如上所示,如若在SQL方式里面未有设置NO_AUTO_VALUE_ON_ZERO的话,那么在暗许设置下,自增列默许一般从一上马自增,插入0也许null代表生成下一个自拉长值。假设用户期望插入的值为0,而该列又是自增加的,那么这几个选项就无法不安装

 

mysql> SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(id, name) value(0, 'kerry');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

+----+-------+

| id | name  |

+----+-------+

|  0 | kerry |

|  1 | kerry |

+----+-------+

2 rows in set (0.00 sec)

 

mysql> 

 

 

 

二:假若把2个NULL值插入到1个AUTO_INCREMENT数据列里去,MySQL将自动生成下多个类别编号。如下所示,那几个语法对于熟知SQL
Server中自增字段的人来来看,几乎正是难以置信的思想政治工作。

 

mysql> drop table if exists test;

Query OK, 0 rows affected (0.03 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32));

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into test(id , name) value(null, 'kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

+----+-------+

| id | name  |

+----+-------+

|  1 | kerry |

+----+-------+

1 row in set (0.00 sec)

 

 

 

三:获取当前自增列的值

 

   
获取当前自增列的值,能够选取 LAST_INSERT_ID函数,注意,那些是2个类别函数,可获取自增列自动生成的最后三个值。但该函数只与服务器的此番对话进程中生成的值有关。要是在与服务器的此番对话中平素不生成AUTO_INCREMENT值,则该函数重返0

 

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

|                1 |

+------------------+

1 row in set (0.00 sec)

 

mysql> insert into test(name) value('jimmy');

Query OK, 1 row affected (0.00 sec)

 

mysql> select last_insert_id();

+------------------+

| last_insert_id() |

+------------------+

|                2 |

+------------------+

1 row in set (0.00 sec)

 

mysql> select * from test;

+----+-------+

| id | name  |

+----+-------+

|  1 | kerry |

|  2 | jimmy |

+----+-------+

2 rows in set (0.00 sec)

 

只要要获取自增列的下三个值,那么能够行使show
create table tablename查看。如下截图所示

 

图片 3

 

 

肆:自增列跳号

 

MySQL中,自增字段能够跳号:可以插入一条钦赐自增列值的笔录(纵然插入的值当先自增列的最大值),如下所示,当前自增列最大值为壹,笔者插入三个200的值,然后就能以200为根基继续自增,而且自身还足以持续插入ID=100的笔录,不要求任何额外设置。

 

 

mysql> select * from test;

+----+-------+

| id | name  |

+----+-------+

|  1 | kerry |

+----+-------+

1 row in set (0.00 sec)

 

mysql> insert into test value(200, 'test');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

+-----+-------+

| id  | name  |

+-----+-------+

|   1 | kerry |

| 200 | test  |

+-----+-------+

2 rows in set (0.00 sec)

 

mysql> insert into test(name) value('test2');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

+-----+-------+

| id  | name  |

+-----+-------+

|   1 | kerry |

| 200 | test  |

| 201 | test2 |

+-----+-------+

3 rows in set (0.00 sec)

 

mysql> 

mysql> insert into test(id, name) value(100, 'ken');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

+-----+-------+

| id  | name  |

+-----+-------+

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

+-----+-------+

4 rows in set (0.00 sec)

 

 

除此以外四个是有关自增列逻辑跳号难题,在多个事务里面,使用碰到事情回滚,自增列就能够跳号,如下所示,id从201跳到 20三了。

 

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(name) value('kkk');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

+-----+-------+

| id  | name  |

+-----+-------+

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

| 202 | kkk   |

+-----+-------+

5 rows in set (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(name) value('kkk');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

+-----+-------+

| id  | name  |

+-----+-------+

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

| 203 | kkk   |

+-----+-------+

5 rows in set (0.00 sec)

 

本来,无论MySQL依然其他关系型数据库,都会遇上那种逻辑跳号的情事,举个例子ORACLE的行列也会存在那种逻辑跳号难题。为抓牢自增列的生成效用,都将生成自增值的操作设计为非事务性操作,表现为当职业回滚时,事务中生成的自增值不会被回滚。

 

伍:truncate
table操作会唤起自增列从头先导计数

 

mysql> truncate table test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into test(name) value('kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

+----+-------+

| id | name  |

+----+-------+

|  1 | kerry |

+----+-------+

1 row in set (0.00 sec)

 

mysql> 

 

6:修改AUTO_INCREMENT的值来修改自增开始值。

 

mysql> select * from test;

+----+-------+

| id | name  |

+----+-------+

|  1 | kerry |

+----+-------+

1 row in set (0.00 sec)

 

mysql> alter table test auto_increment=100;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into test(name) value('k3');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

+-----+-------+

| id  | name  |

+-----+-------+

|   1 | kerry |

| 100 | k3    |

+-----+-------+

2 rows in set (0.00 sec)

 

自然MySQL还有一部分连锁知识点,这里没有做总计,主若是绝非越过过有关景况。今后境遇了再做总计,其它壹方面,写技术作品,很难面面俱圆,那样太耗费时间也太累人了!

 

 

 

参考资料:

 

http://www.cnblogs.com/TeyGao/p/9279390.html

https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

http://www.cnblogs.com/yangzumin/p/3756583.html

留下评论

网站地图xml地图