MYSQL和ORACLE自增列的区别

逃离我推掉我的手 2022-09-25 05:24 260阅读 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的区别

相关 mysql 自增列新增

相关 MYSQL和ORACLE自增列的区别

相关 oracle自增

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

相关 mysql自增和orcale自增

相关 Oracle列自增-12c