1． 自动增长的数据类型处理
MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。
oracle自增方式：
--第一步：创建SEQUENCE
create sequence cmdb.M_MODULE_seq increment by 1 start with 1;
--第二步：创建一个基于该表的before insert 触发器，在触发器中使用刚创建的SEQUENCE
create or replace trigger cmdb.M_MODULE_trigger
before insert on cmdb.M_MODULE
for each row
begin
select cmdb.M_MODULE_seq.nextval into:new.id from sys.dual ;
end;
重点说mysql自增方式：
说明：
自增字段的数据类型必须为：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT中的一种，并且必须作为主键或联合主键的一部分。若是作为主键的一部分，自增序列字段值将会丧失唯一性保证。AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效
mysql> create table user(
-> userid int ( 4 ) primary key not null auto_increment,
-> username varchar(16 ) not null ,
-> userpassword varchar(32 ) not null
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> alter table user auto_increment=1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into user(username,userpassword) values('ddd','asda');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+--------+----------+--------------+
| userid | username | userpassword |
+--------+----------+--------------+
| 1 | ddd | asda |
+--------+----------+--------------+
1 row in set (0.00 sec)
这里我们发现问题：
mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`userid` int(4) NOT NULL AUTO_INCREMENT,
`username` varchar(16) NOT NULL,
`userpassword` varchar(32) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB
AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec
AUTO_INCREMENT=2是指自增序列将要增长的下一个值，总是按照设定的比userid前一个值大一。
mysql> insert into user values(10,'ddd','asda');
Query OK, 1 row affected (0.00 sec)
mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`userid` int(4) NOT NULL AUTO_INCREMENT,
`username` varchar(16) NOT NULL,
`userpassword` varchar(32) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
当执行更新将userid字段改到比AUTO_INCREMENT=11只更大时，下一个插入的值不会继续向最大的值增长（不会是13），而会是11
mysql> update user set userid=12 where userid=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`userid` int(4) NOT NULL AUTO_INCREMENT,
`username` varchar(16) NOT NULL,
`userpassword` varchar(32) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from user;
+--------+----------+--------------+
| userid | username | userpassword |
+--------+----------+--------------+
| 1 | ddd | asda |
| 2 | ddd | asda |
| 12 | ddd | asda |
+--------+----------+--------------+
3 rows in set (0.00 sec)
mysql> insert into user(username,userpassword) values('aaaa','ddddd');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+--------+----------+--------------+
| userid | username | userpassword |
+--------+----------+--------------+
| 1 | ddd | asda |
| 2 | ddd | asda |
| 11 | aaaa | ddddd |
| 12 | ddd | asda |
+--------+----------+--------------+
4 rows in set (0.00 sec)
但是继续插入数据会报重复的错误，因为自增为表的userid字段分配的值是12，AUTO_INCREMENT=12 ，与已经有数据重复
mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`userid` int(4) NOT NULL AUTO_INCREMENT,
`username` varchar(16) NOT NULL,
`userpassword` varchar(32) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into user(username,userpassword) values('aaaa','ddddd');
ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'
此时需要找到此时userid最大值
mysql> select max(user.userid) from user;
+------------------+
| max(user.userid) |
+------------------+
| 12 |
+------------------+
1 row in set (0.00 sec)
然后将auto_increment设置为比他大1即13
mysql> alter table user auto_increment=13;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
问题可以解决
MySQL还提供了两个全局参数AUTO_INCREMENT_INCREMENT与AUTO_INCREMENT_OFFSET，用于配置MySQL实例的自增序列增长起始值与步长。
AUTO_INCREMENT_INCREMENT= n ，则设置自增序列增长的步长为n；
AUTO_INCREMENT_OFFSET=m，则设置自增序列的起始位置为m；
若使用这个特性与复制功能结合的话，就可以方便地实现Master-Master的两边读写。例如mmm架构，以及更多的应用扩展。
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
改变节点步长，和其实位置值
mysql> CREATE TABLE fafa_quto_incr (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)
mysql> SET @@auto_increment_offset=10;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @@auto_increment_increment=3;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 3 |
| auto_increment_offset | 10 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO fafa_quto_incr VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from fafa_quto_incr;
+-----+
| col |
+-----+
| 3 |
| 6 |
| 9 |
| 10 |
+-----+
4 rows in set (0.00 sec)
当auto_increment_offset 的值比auto_increment_increment大时, auto_increment_offset 值被忽略
所以我们一般设置auto_increment_increment大于auto_increment_offset
mysql> SET @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @@auto_increment_offset=2;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 2 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO fafa_quto_incr VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from fafa_quto_incr;
+-----+
| col |
+-----+
| 2 |
| 12 |
| 22 |
| 32 |
+-----+
4 rows in set (0.00 sec)
转载请注明原文地址: https://ju.6miu.com/read-1297163.html
