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

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]