sql server ,mysql ,oracle 自增长字段设置以及自增长属性的修改验证

小鱼儿 2022-08-19 01:48 337阅读 0赞

sql server ,mysql ,oracle 自增长字段设置

sql server 和mysql都有自增长的功能，而Oracle通过sequence序列,并使用触发器才能够实现自动增长

**mysql自增设置**

create table autoincrement(id int auto\_increment primary key not null, name varchar(20));  
mysql中：  
创建含有自增长字段的表  
mysql> create table autoincrement(id int auto\_increment primary key not null, n  
me varchar(20));  
Query OK, 0 rows affected (0.42 sec)  
插入数据  
mysql> insert into autoincrement(name) values('auto1');  
Query OK, 1 row affected (0.12 sec)  
mysql> insert into autoincrement(name) values('auto2');  
Query OK, 1 row affected (0.12 sec)  
mysql> select \* from autoincrement;  
\+----+-------+  
| id | name  |  
\+----+-------+  
|  1 | auto1 |  
|  2 | auto2 |  
\+----+-------+  
2 rows in set (0.02 sec)  
  
在自增长字段上插入指定值的数据  
mysql> insert into autoincrement(id,name) values(3,'auto2');  
Query OK, 1 row affected (0.12 sec)  
mysql> insert into autoincrement(id,name) values(4,'auto2');  
Query OK, 1 row affected (0.11 sec)  
mysql> insert into autoincrement(id,name) values(10,'auto2');  
Query OK, 1 row affected (0.07 sec)  
mysql> select \* from autoincrement;  
\+----+-------+  
| id | name  |  
\+----+-------+  
|  1 | auto1 |  
|  2 | auto2 |  
|  3 | auto2 |  
|  4 | auto2 |  
| 10 | auto2 |  
\+----+-------+  
5 rows in set (0.00 sec)  
查询自增长的起始值变化：  
mysql> SELECT AUTO\_INCREMENT FROM information\_schema.tables WHERE table\_name="autoincrement";  
\+----------------+  
| AUTO\_INCREMENT |  
\+----------------+  
|             11 |  
\+----------------+  
1 row in set (0.00 sec)  
从结果可以看到自增长的初始值变为11了，也就是变为自增长字段的最大值+步长了  
再次插入自增长，下次的ID为11  
mysql> insert into autoincrement(name) values('auto11');  
Query OK, 1 row affected (0.11 sec)  
mysql> select \* from autoincrement;  
\+----+--------+  
| id | name   |  
\+----+--------+  
|  1 | auto1  |  
|  2 | auto2  |  
|  3 | auto2  |  
|  4 | auto2  |  
| 10 | auto2  |  
| 11 | auto11 |  
\+----+--------+  
修改自增长字段的起始值：  
ALTER TABLE autoincrement auto\_increment=30 ;  
mysql> SELECT AUTO\_INCREMENT FROM information\_schema.tables WHERE table\_name="autoincrement";  
\+----------------+  
| AUTO\_INCREMENT |  
\+----------------+  
|             30 |  
\+----------------+  
1 row in set (0.00 sec)  
再次插入数据，id应为30  
mysql> insert into autoincrement(name) values('auto30');  
Query OK, 1 row affected (0.07 sec)  
mysql> select \* from autoincrement;  
\+----+--------+  
| id | name   |  
\+----+--------+  
|  1 | auto1  |  
|  2 | auto2  |  
|  3 | auto2  |  
|  4 | auto2  |  
| 10 | auto2  |  
| 11 | auto11 |  
| 30 | auto30 |  
\+----+--------+  
  
不能用alter table 改变自增长的步长的值  
mysql> ALTER TABLE autoincrement auto\_increment\_increment=30;  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that  
corresponds to your MySQL server version for the right syntax to use near 'auto\_  
increment\_increment=30' at line 1  
可以会话级别修改或者写到配置文件中  
set session auto\_increment\_increment=2  
set session auto\_increment\_offset=2  
set global auto\_increment\_increment=2  
set global auto\_increment\_offset=2  
重启mysql后就恢复原来的值了  
如果永久生效加到配置文件中：  
\[mysqld\]  
auto\_increment\_increment= 2  
auto\_increment\_offset   = 1  
  
MySQL中对于表上ID自增列可以在创建表的时候来指定列上的auto\_increment属性；  
等同于SQL server中的identity属性；Oracle则是通过Sequence方式来实现。  
在MySQL中系统变量auto\_increment\_increment，auto\_increment\_offset 影响自增列的值及其变化规则  
auto\_increment\_offset表示自增长字段从那个数开始  
auto\_increment\_increment表示自增长字段每次递增的量，其默认值是1  
  
一个表中只能有一个自增长字段（多于2个自增长字段会报错）  
mysql> create table autoincrement1(id1 int auto\_increment primary key not null,i  
d2 int auto\_increment not null,name varchar(20));  
ERROR 1075 (42000): Incorrect table definition; there can be only one auto colum  
n and it must be defined as a key  
mysql>  
truncate命令是会把自增的字段还原为从1开始的，而delete不会  
  
取消自增长字段  
ALTER TABLE \`autoincrement\` MODIFY COLUMN \`id\`  int(11) NOT NULL FIRST ;  
mysql> desc autoincrement;  
\+-------+-------------+------+-----+---------+----------------+  
| Field | Type        | Null | Key | Default | Extra          |  
\+-------+-------------+------+-----+---------+----------------+  
| id    | int(11)     | NO   | PRI | NULL    | auto\_increment |  
| name  | varchar(20) | YES  |     | NULL    |                |  
\+-------+-------------+------+-----+---------+----------------+  
2 rows in set (0.03 sec)  
mysql>  
mysql> ALTER TABLE \`autoincrement\` MODIFY COLUMN \`id\`  int(11) NOT NULL FIRST ;  
Query OK, 0 rows affected (0.20 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
mysql> desc autoincrement;  
\+-------+-------------+------+-----+---------+-------+  
| Field | Type        | Null | Key | Default | Extra |  
\+-------+-------------+------+-----+---------+-------+  
| id    | int(11)     | NO   | PRI | NULL    |       |  
| name  | varchar(20) | YES  |     | NULL    |       |  
\+-------+-------------+------+-----+---------+-------+  
2 rows in set (0.02 sec)  
mysql>  
  
修改字段为自增长  
ALTER TABLE \`autoincrement\` MODIFY COLUMN \`id\`  int(11) NOT NULL AUTO\_INCREMENT FIRST ;  
mysql> ALTER TABLE \`autoincrement\` MODIFY COLUMN \`id\`  int(11) NOT NULL AUTO\_IN  
REMENT FIRST ;  
Query OK, 0 rows affected (0.23 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
mysql> desc autoincrement;  
\+-------+-------------+------+-----+---------+----------------+  
| Field | Type        | Null | Key | Default | Extra          |  
\+-------+-------------+------+-----+---------+----------------+  
| id    | int(11)     | NO   | PRI | NULL    | auto\_increment |  
| name  | varchar(20) | YES  |     | NULL    |                |  
\+-------+-------------+------+-----+---------+----------------+  
2 rows in set (0.01 sec)  
  
**sql server自增字段：**  
创建含有自增长字段的表（从2开始增长，增量为1）  
CREATE TABLE testincrement(id int NOT NULL IDENTITY (2, 1),name nvarchar(50) NULL)  
命令已成功完成。  
select \* from testincrement;  
id name  
2  test1  
3  test2  
在自增长字段上插入指定值的数据  
insert into testincrement(id,name) values (6,'test2');  
报错：  
消息 544，级别 16，状态 1，第 1 行  
当 IDENTITY\_INSERT 设置为 OFF 时，不能为表 'testincrement' 中的标识列插入显式值。  
设置IDENTITY\_INSERT 为on 插入成功，只在会话级别生效  
set IDENTITY\_INSERT testincrement on  
命令已成功完成。  
select \* from testincrement;  
id name  
2 test1  
3 test2  
6 test2  
  
再次执行  
insert into testincrement(name) values ('test7');  
报错  
消息 545，级别 16，状态 1，第 1 行  
当 IDENTITY\_INSERT 设置为 ON 或某个复制用户向 NOT FOR REPLICATION 标识列中插入内容时，必须为表 'testincrement' 中的标识列指定显式值。  
执行：  
set IDENTITY\_INSERT testincrement off  
insert into testincrement(name) values ('test7');  
(1 行受影响)  
select \* from testincrement;  
id name  
2 test1  
3 test2  
6 test2  
7 test7  
可以看出自增字段从id中的最大值开始自增  
  
获取自增字段的当前值  
select ident\_current('testincrement')  
7  
修改自增字段的起始值为20  
用Microsoft SQL Server Management Studio工具修改生成的脚本如下：  
BEGIN TRANSACTION  
GO  
CREATE TABLE dbo.Tmp\_testincrement  
(  
id int NOT NULL IDENTITY (20, 1),  
name nvarchar(50) NULL  
)  ON \[PRIMARY\]  
GO  
ALTER TABLE dbo.Tmp\_testincrement SET (LOCK\_ESCALATION = TABLE)  
GO  
SET IDENTITY\_INSERT dbo.Tmp\_testincrement ON  
GO  
IF EXISTS(SELECT \* FROM dbo.testincrement)  
EXEC('INSERT INTO dbo.Tmp\_testincrement (id, name)  
SELECT id, name FROM dbo.testincrement WITH (HOLDLOCK TABLOCKX)')  
GO  
SET IDENTITY\_INSERT dbo.Tmp\_testincrement OFF  
GO  
DROP TABLE dbo.testincrement  
GO  
EXECUTE sp\_rename N'dbo.Tmp\_testincrement', N'testincrement', 'OBJECT'   
GO  
COMMIT  
新建临时表设置初始值为20然后把表中的数据迁移到临时表中，删除原表，然后重命名表名称  
可以通过dbcc重置标示值  
\--重置标识值  
DBCC CHECKIDENT('testincrement', RESEED, 20)  
检查标识信息: 当前标识值 '8'，当前列值 '20'。  
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。  
insert into testincrement(name) values ('test7');  
select \* from testincrement;  
id name  
2 test1  
3 test2  
6 test2  
7 test7  
8 test7  
21 test7  
修改自增字段的步长  
BEGIN TRANSACTION  
GO  
CREATE TABLE dbo.Tmp\_testincrement  
(  
id int NOT NULL IDENTITY (2, 10),  
name nvarchar(50) NULL  
)  ON \[PRIMARY\]  
GO  
ALTER TABLE dbo.Tmp\_testincrement SET (LOCK\_ESCALATION = TABLE)  
GO  
SET IDENTITY\_INSERT dbo.Tmp\_testincrement ON  
GO  
IF EXISTS(SELECT \* FROM dbo.testincrement)  
EXEC('INSERT INTO dbo.Tmp\_testincrement (id, name)  
SELECT id, name FROM dbo.testincrement WITH (HOLDLOCK TABLOCKX)')  
GO  
SET IDENTITY\_INSERT dbo.Tmp\_testincrement OFF  
GO  
DROP TABLE dbo.testincrement  
GO  
EXECUTE sp\_rename N'dbo.Tmp\_testincrement', N'testincrement', 'OBJECT'   
GO  
COMMIT  
与修改自增字段初始值操作过程一样  
删除字段自增属性：  
BEGIN TRANSACTION  
GO  
CREATE TABLE dbo.Tmp\_testincrement  
(  
id int NOT NULL,  
name nvarchar(50) NULL  
)  ON \[PRIMARY\]  
GO  
ALTER TABLE dbo.Tmp\_testincrement SET (LOCK\_ESCALATION = TABLE)  
GO  
IF EXISTS(SELECT \* FROM dbo.testincrement)  
EXEC('INSERT INTO dbo.Tmp\_testincrement (id, name)  
SELECT id, name FROM dbo.testincrement WITH (HOLDLOCK TABLOCKX)')  
GO  
DROP TABLE dbo.testincrement  
GO  
EXECUTE sp\_rename N'dbo.Tmp\_testincrement', N'testincrement', 'OBJECT'   
GO  
COMMIT  
过程是新临时表迁移数据删除并重名表  
  
设置表中某个已经存在字段为自增长字段过程和上面一样，也需要数据迁移  
每个表只能指定一个自增长的列  
CREATE TABLE dbo.Table\_1  
(  
id1 int NOT NULL IDENTITY (1, 1),  
id2 int NOT NULL IDENTITY (1, 1)  
)   
消息 2744，级别 16，状态 2，第 1 行  
为表 'Table\_1' 指定了多个标识列。只允许为每个表指定一个标识列。  
sql server中修改表自增长，需要迁移数据，对于数据量很大的表，修改需要时间很长  
  
**oracle 自增长字段**  
oracle和其他数据不一样通过序列来实现自增长，与表无关  
首先新建序列  
     create sequence auto\_increment  
     minvalue 1  
     maxvalue 999999999999999999  
     start with 1  
     increment by 1  
     nocache;  
插入数据需要通过  
insert into testincrement(id,name) values (auto\_increment.nextval'test7');  
需要的起始值步长可以通过alter sequence 来进行修改  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
CREATE TABLE dbo.Table\_1  
(  
id int NOT NULL,  
name nvarchar(50) NULL,  
id2 int NOT NULL IDENTITY (1, 1)  
)  ON \[PRIMARY\]