MYSQL和ORACLE自增列的区别

逃离我推掉我的手 2022-09-25 05:24 284阅读 0赞

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;

| 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;

| 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;

| 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)

MYSQL和ORACLE自增列的区别

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql创建自增列(mysql建表语句自增)

相关 mysql自增列设置步长

相关 MYSQL中GUID和自增列做主键的优缺点

相关 mysql主键自增和UUID的区别

相关 JavaScript 自增和自减的区别

相关 mysql 自增列新增

相关 MYSQL和ORACLE自增列的区别

相关 MYSQL查询结果自增序号列

相关 mysql自增和orcale自增

相关 Oracle列自增-12c