更新时间:2023-09-06 GMT+08:00
自增字段取值
GaussDB(for MySQL)对自增字段的赋值有以下几种方法:
# 表结构 CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) );
- 不对自增字段赋值,数据库会自动将自增值填入字段中,AUTO_INCREMENT自增。
- 插入数据。
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');
- 查询表数据。
select * from animals; +----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+
- 查询表结构。
show create table animals; +---------+--------------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------+ | animals | CREATE TABLE `animals` ( `id` mediumint NOT NULL AUTO_INCREMENT, `name` char(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | +---------+--------------------------------------------------+
- 插入数据。
- 对自增字段赋0或null值,数据库会自动将自增值填入字段中。AUTO_INCREMENT自增。
- 插入数据。
INSERT INTO animals (id,name) VALUES(0,'groundhog'); INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
- 查询数据。
select * from animals; +----+-----------+ | id | name | +----+-----------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | | 7 | groundhog | | 8 | squirrel | +----+-----------+ 8 rows in set (0.00 sec)
- 查询表结构。
show create table animals; +---------+----------------------------------------------------------------+ | Table | Create Table | +---------+----------------------------------------------------------------+ | animals | CREATE TABLE `animals` ( `id` mediumint NOT NULL AUTO_INCREMENT, `name` char(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 | +---------+------------------------------------------------------------+
- 插入数据。
- 直接使用大于AUTO_INCREMENT的值A,数据库会将A填入字段并修改AUTO_INCREMENT=A+1。
- 插入数据。
INSERT INTO animals (id,name) VALUES(100,'rabbit');
- 查询数据。
select * from animals; +-----+-----------+ | id | name | +-----+-----------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | | 7 | groundhog | | 8 | squirrel | | 100 | rabbit | +-----+-----------+ 9 rows in set (0.00 sec)
- 查询表结构。
show create table animals; +---------+----------------------------------------------------------------------+ | Table | Create Table | +---------+----------------------------------------------------------------------+ | animals | CREATE TABLE `animals` ( `id` mediumint NOT NULL AUTO_INCREMENT, `name` char(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 | +---------+----------------------------------------------------------------------+
- 插入数据。
- 使用小于AUTO_INCREMENT,但不冲突的值。数据可以插入,但AUTO_INCREMENT不变。
mysql> INSERT INTO animals (id,name) VALUES(50,'middle'); Query OK, 1 row affected (0.00 sec) mysql> select * from animals; +-----+-----------+ | id | name | +-----+-----------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | | 7 | groundhog | | 8 | squirrel | | 50 | middle | | 100 | rabbit | +-----+-----------+ 10 rows in set (0.00 sec) mysql> show create table animals; +---------+------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------+ | animals | CREATE TABLE `animals` ( `id` mediumint NOT NULL AUTO_INCREMENT, `name` char(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 | +---------+------------------------------------------------------------------+
- 使用负值数据可以插入,但AUTO_INCREMENT不变。
- 插入数据。
INSERT INTO animals (id,name) VALUES(-50,'-middle');
- 查询数据。
select * from animals; +-----+-----------+ | id | name | +-----+-----------+ | -50 | -middle | | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | | 7 | groundhog | | 8 | squirrel | | 50 | middle | | 100 | rabbit | +-----+-----------+ 11 rows in set (0.00 sec)
- 查询表结构。
show create table animals; +---------+------------------------------------------------------------------------------------+ | Table | Create Table +---------+------------------------------------------------------------------------------------+ | animals | CREATE TABLE `animals` ( `id` mediumint NOT NULL AUTO_INCREMENT, `name` char(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 | +---------+-----------------------------------------------------------------------------------+
- 插入数据。
父主题: 基本使用类