查看GaussDB(for MySQL)的存储容量
GaussDB(for MySQL)是存储计算分离架构，数据存储在共享存储系统中，共享存储容量可以通过管理控制台看到，详情请参考如下步骤操作，数据每30分钟更新一次。
操作步骤
步骤 1 登录管理控制台。
步骤 2 单击管理控制台右上角的
，选择Region。
步骤 3 在页面左上角单击
，选择“数据库 > 云数据库 GaussDB(for MySQL)”。
步骤 4 在实例列表中，单击目标实例名称，进入实例的“基本信息”页面。
步骤 5 在基本信息页面的“存储“存储/备份空间”模块可以看到当前实例占用的共享存储容量。
说明GaussDB(for MySQL)存储容量的计算与传统MySQL有一定的区别，与传统MySQL使用（数据大小+索引大小+空闲空间）计算的容量数据会有一定的差别。
如果要查询精确的存储使用量，可以使用管理控制台查询或者连接GaussDB(for MySQL)数据库后，执行show spaceusage;命令查看当前数据使用的存储容量，该值为精确值，非估算值。
图 查看存储容量
- 共享存储
- 显示的使用状况就是该实例购买的包周期的共享存储容量及目前数据已占用的容量。
- 如果已使用空间超过购买的共享存储容量，GaussDB(for MySQL)会自动扩容，无需担心磁盘满带来的业务问题。
- 自动扩容的空间会按照按需使用的收费标准收取，建议超出后使用磁盘容量变更功能扩展包周期的存储容量。
- 备份空间：
系统会赠送一份与包周期或按需存储容量相同大小的备份空间。
执行show spaceusage;命令查看存储容量，其值等于表数据、表预分配空间、分区预分配空间、Binlog、Redolog和Undolog之和，详情见下表：
| 条目 | 查看方式 | 说明 |
|---|---|---|
| 表数据 | select sum(data_length+index_length+data_free) from information_schema.tables; | 传统MySQL的容量计算方式，该语句依赖统计数据的精准度，在统计数据未更新时可能会有偏差。 |
| 表预分配空间 | select count(*) from information_schema.tables; | 每张表会预分配4MB空间，该语句查询出表的数量乘以4MB就是总的表预分配空间。 |
| 分区预分配空间 | select count(*) from INFORMATION_SCHEMA.PARTITIONS where PARTITION_NAME is not null; | 每个分区会预分配4MB空间，该语句查询出分区的数量乘以4MB就是总的分区预分配空间。 |
| Binlog | show binary logs; | 将所有binlog的文件大小相加。 |
| Redolog | show lsninfo; | flushed_to_disk_lsn- truncate_lsn |
| Undolog | 无法直接查看 | 需要时可咨询客服人员。 |
修改库名和修改表名
对于库重命名和表重命名，GaussDB(for MySQL)与社区MySQL的用法是相同的。
- 支持修改表名：rename table a to b; 注意，该语句是可以跨库执行的，比如：rename table da.ta to db.ta;是将ta表从da库移动到db库。
- 不支持修改库名，如果有修改库名的需求，可以先创建新的库名，然后借助rename table的跨库执行将所有表从原库移动到新库，然后删除原库。语句示例：
# 进入原库
use ta;
# 列出原库的所有表名
Show tables;
# 查看原库的创建语句
Show create database ta;
# 使用原库的创建语句创建新库(只改库名，其他参数照抄，这样能尽量保证新库与原库的各类参数相同)
create database tb；
# 将原库所有表移动至新库
rename table da.ta to db.ta;
rename table da.tb to db.tb;
rename table da.tc to db.tc;
…
# 删除原库
Drop database ta;
字符集和字符序的默认选择方式
相关变量设置
参数组中默认character_set_server=utf8、collation_server=utf8_general_ci，可以在界面修改参数值。
默认选择方式
- 在创建数据库时，如果未显式指定库的字符集和字符序，则库的字符集和字符序采用character_set_server和collation_server参数的值；如果显式指定，则使用指定的字符集和字符序。
- 在创建数据表时，如果未显式指定表的字符集和字符序，则表默认字符集和字符序使用所在数据库的字符集和字符序；如果显式指定，则使用指定的字符集和字符序。
- 在创建数据表时，如果未显式指定字段的字符集和字符序，则字段使用所在表的字符集和字符序；如果显式指定，则使用指定的字符集和字符序。
示例1：不显式指定字符集、字符序的情况下创建数据库和数据表。
示例2：显式指定库的字符集、字符序的情况下创建数据库。
示例3：显式指定表的字符集、字符序的情况下创建数据表。
示例4：显式指定字段的字符集、字符序的情况下创建数据表。
自增字段值跳变的原因
本节介绍了自增字段相关问题与处理方法。
数据表中的自增字段取值不是连续的，自增值跳变。
出现表中的自增字段取值不连续的情况，可能原因有以下几种：
- 初值与步长问题，步长不为1会导致自增字段取值不连续。
# 进入原库
use ta;
# 列出原库的所有表名
Show tables;
# 查看原库的创建语句
Show create database ta;
# 使用原库的创建语句创建新库(只改库名，其他参数照抄，这样能尽量保证新库与原库的各类参数相同)
create database tb；
# 将原库所有表移动至新库
rename table da.ta to db.ta;
rename table da.tb to db.tb;
rename table da.tc to db.tc;
…
# 删除原库
Drop database ta;
- 直接修改表的AUTO_INCREMENT，会导致自增字段取值跳变。
# 进入原库
use ta;
# 列出原库的所有表名
Show tables;
# 查看原库的创建语句
Show create database ta;
# 使用原库的创建语句创建新库(只改库名，其他参数照抄，这样能尽量保证新库与原库的各类参数相同)
create database tb；
# 将原库所有表移动至新库
rename table da.ta to db.ta;
rename table da.tb to db.tb;
rename table da.tc to db.tc;
…
# 删除原库
Drop database ta;
- 插入数据时直接指定自增字段的取值，会导致自增字段取值跳变。
mysql> select * from animals;
+----+-----------+
| id | name |
+----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
+----+-----------+
mysql> INSERT INTO animals (id,name) VALUES(100,'rabbit');
Query OK, 1 row affected (0.00 sec)
mysql> select * from animals;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 100 | rabbit |
+-----+-----------+
9 rows in set (0.00 sec)
- 未提交的事务或回滚的事务，会导致AUTO_INCREMENT增长，但回滚后不会下降。后续如果再次插入数据就会导致数据中的自增字段发生跳变。
mysql> show create table auto_test1;
+------------+----------------------------------------+
| Table | Create Table |
+------------+----------------------------------------+
| auto_test1 | CREATE TABLE `auto_test1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from auto_test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
mysql> begin;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into auto_test1 values (0),(0),(0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from auto_test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)
mysql> show create table auto_test1;
+------------+----------------------------------------+
| Table | Create Table |
+------------+----------------------------------------+
| auto_test1 |
CREATE TABLE `auto_test1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from auto_test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> show create table auto_test1;
+------------+----------------------------------------+
| Table | Create Table |
+------------+----------------------------------------+
| auto_test1 | CREATE TABLE `auto_test1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+------------+----------------------------------------+
mysql> insert into auto_test1 values (0),(0),(0);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from auto_test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 7 |
| 8 |
| 9 |
+----+
6 rows in set (0.00 sec)
mysql> show create table auto_test1;
+------------+-----------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------+
| auto_test1 | CREATE TABLE `auto_test1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------+
- 数据插入后，AUTO_INCREMENT变化，然后删除对应的数据行，AUTO_INCREMENT不会下降，后续如果再次插入数据就会导致数据中的自增字段发生跳变。
mysql> show create table auto_test1;
+------------+----------------------------------------+
| Table | Create Table |
+------------+----------------------------------------+
| auto_test1 | CREATE TABLE `auto_test1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from auto_test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
mysql> insert into auto_test1 values (0),(0),(0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from auto_test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)
mysql> show create table auto_test1;
+------------+----------------------------------------+
| Table | Create Table |
+------------+----------------------------------------+
| auto_test1 | CREATE TABLE `auto_test1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> delete from auto_test1 where id>3;
mysql> select * from auto_test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> show create table auto_test1;
+------------+----------------------------------------+
| Table | Create Table |
+------------+----------------------------------------+
| auto_test1 | CREATE TABLE `auto_test1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+------------+----------------------------------------+
mysql> insert into auto_test1 values (0),(0),(0);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from auto_test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 7 |
| 8 |
| 9 |
+----+
6 rows in set (0.00 sec)
mysql> show create table auto_test1;
+------------+-----------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------+
| auto_test1 | CREATE TABLE `auto_test1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------+
- 因为一些原因（比如唯一键冲突），使得插入数据最终未成功的，有可能导致AUTO_INCREMENT跳变。
mysql> create table auto_test7(`id` int NOT NULL AUTO_INCREMENT, cred_id int UNIQUE, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.64 sec)
mysql> insert into auto_test7 values(null, 1);
Query OK, 1 row affected (0.03 sec)
mysql> show create table auto_test7;
+------------+-------------------------------+
| Table | Create Table |
+------------+-------------------------------+
| auto_test7 | CREATE TABLE `auto_test7` ( `id` int NOT NULL AUTO_INCREMENT, `cred_id` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cred_id` (`cred_id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into auto_test7 values(null, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'auto_test7.cred_id'
mysql> show create table auto_test7;
+------------+--------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------+
| auto_test7 | CREATE TABLE `auto_test7` ( `id` int NOT NULL AUTO_INCREMENT, `cred_id` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cred_id` (`cred_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+------------+---------------------------------------------------------------+
- 批量插入数据时（如insert...select、load file等），自增键的申请是分批申请的，每批申请2的n次方个序号，用完继续申请，没用完也不会退回，所以可能会导致AUTO_INCREMENT跳变。
mysql> create table auto_test7(`id` int NOT NULL AUTO_INCREMENT, cred_id int UNIQUE, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.64 sec)
mysql> insert into auto_test7 values(null, 1);
Query OK, 1 row affected (0.03 sec)
mysql> show create table auto_test7;
+------------+-------------------------------+
| Table | Create Table |
+------------+-------------------------------+
| auto_test7 | CREATE TABLE `auto_test7` ( `id` int NOT NULL AUTO_INCREMENT, `cred_id` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cred_id` (`cred_id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into auto_test7 values(null, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'auto_test7.cred_id'
mysql> show create table auto_test7;
+------------+--------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------+
| auto_test7 | CREATE TABLE `auto_test7` ( `id` int NOT NULL AUTO_INCREMENT, `cred_id` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cred_id` (`cred_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+------------+---------------------------------------------------------------+
表的自增AUTO_INCREMENT初值与步长
数据表中自增字段的AUTO_INCREMENT的初值与步长由auto_increment_increment和auto_increment_offset参数决定。
- auto_increment_offset：AUTO_INCREMENT值的初值。
- auto_increment_increment：AUTO_INCREMENT值每次增长的步长。
- 当auto_increment_offset > auto_increment_increment 时，实际使用时初值会变为auto_increment_increment。
- 当auto_increment_offset <= auto_increment_increment，自增值计算方式如下：
自增值= auto_increment_offset + N*auto_increment_increment （N为插入的数据条数）
在GaussDB(for MySQL)中这两个参数默认值都为1，参考如下步骤修改。如需修改时需要在控制台-实例详情-参数修改中修改。
步骤 1 登录管理控制台。
步骤 2 单击管理控制台右上角的
，选择Region。
步骤 3 在页面左上角单击
，选择“数据库 > 云数据库 GaussDB(for MySQL)”。
步骤 4 在“实例管理”页面，选择指定的实例，单击实例名称，进入实例的基本信息页面。
步骤 5 在左侧导航栏中选择“参数修改”，在“参数”页签修改相应参数。
示例：
- auto_increment_offset=1，auto_increment_increment=1，表示初值为1，步长为1。
show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
- 修改auto_increment_increment=2，步长变为2。
set session auto_increment_offset=2;
Query OK, 0 rows affected (0.02 sec)
show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 1 |
+--------------------------+-------+
- auto_increment_offset=10，auto_increment_increment=2，由于auto_increment_offset > auto_increment_increment，因此初值为2，步长为2。
set session auto_increment_offset=10;
set session auto_increment_increment=2;
show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 10 |
+--------------------------+-------+
create table auto_test2(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.08 sec)
show create table auto_test2;
CREATE TABLE `auto_test2` ( `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
insert into auto_test2 values(0), (0), (0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
select * from auto_test2;
+----+
| id |
+----+
| 2 |
| 4 |
| 6 |
+----+
3 rows in set (0.01 sec)
- auto_increment_offset=5，auto_increment_increment=10，初值为5，步长为10。
set session auto_increment_offset=10;
set session auto_increment_increment=2;
show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 10 |
+--------------------------+-------+
create table auto_test2(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.08 sec)
show create table auto_test2;
CREATE TABLE `auto_test2` ( `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
insert into auto_test2 values(0), (0), (0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
select * from auto_test2;
+----+
| id |
+----+
| 2 |
| 4 |
| 6 |
+----+
3 rows in set (0.01 sec)
修改表的自增AUTO_INCREMENT值
AUTO_INCREMENT修改时，遵循如下约束限制：
- 当AUTO_INCREMENT大于表中数据的最大值时，可以在取值范围内任意修改为更大的值。
set session auto_increment_offset=10;
set session auto_increment_increment=2;
show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 10 |
+--------------------------+-------+
create table auto_test2(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.08 sec)
show create table auto_test2;
CREATE TABLE `auto_test2` ( `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
insert into auto_test2 values(0), (0), (0);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
select * from auto_test2;
+----+
| id |
+----+
| 2 |
| 4 |
| 6 |
+----+
3 rows in set (0.01 sec)
- 当AUTO_INCREMENT大于表中数据的最大值时，如果修改后的指定值仍大于数据的最大值，则修改为指定值成功。否则，默认会修改为数据最大值+1。
mysql> select * from animals;
+-----+-----------+
| id | name |
+-----+-----------+
| -50 | -middle |
| 1 | dog |
| 2 | cat |
| 50 | middle |
| 100 | rabbit |
+-----+-----------+
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=200 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------+
mysql> alter table animals AUTO_INCREMENT=150;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
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=150 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------+
mysql> alter table animals AUTO_INCREMENT=50;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 |
+---------+-----------------------------------------------------+
mysql> delete from animals where id=100;
Query OK, 1 row affected (0.00 sec)
mysql> select * from animals;
+-----+-----------+
| id | name |
+-----+-----------+
| -50 | -middle |
| 1 | dog |
| 2 | cat |
| 50 | middle |
+-----+-----------+
10 rows in set (0.00 sec)
mysql> alter table animals AUTO_INCREMENT=50;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
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=51 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------+
1 row in set (0.00 sec)
- AUTO_INCREMENT无法修改为负数。
alter table animals AUTO_INCREMENT=-1;
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 '-1' at line 1
自增主键达到上限，无法插入数据
场景描述
插入数据时报错ERROR 1062 (23000): Duplicate entry 'xxx' for key 'xxx'。
原因分析
自增主键的字段取值达到上限，无法继续增长，导致新插入的数据生成的自增主键值与表中上一条数据相同，因为自增主键的值不可重复，插入失败报错。
解决方案
- 如果数据变化较多，表中实际数据量远小于自增主键的容量，则可以考虑将该表的数据全量导入新表，删除原表，然后rename将新表名改回原表名。(使用数据导入导出的方法有多种实现方法，此处仅举其中一种例子)
a. 创建表auto_test5_tmp。
create table auto_test5_tmp(id tinyint not null AUTO_INCREMENT, name varchar(8), PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.07 sec)
b. 插入数据。
insert into auto_test5_tmp select 0,name from auto_test5;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
c. 查询表数据。
select * from auto_test5_tmp;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | X |
| 5 | Y |
| 6 | Z |
+----+------+
d. 删除表。
drop table auto_test5;
e. 重命名。
rename table auto_test5_tmp to auto_test5;
Query OK, 0 rows affected (0.12 sec)
- 如果自增主键的取值范围不够，则修改自增主键的字段类型。
alter table auto_test6 modify column id int NOT NULL AUTO_INCREMENT;
Query OK, 6 rows affected (0.15 sec)
Records: 6 Duplicates: 0 Warnings: 0
自增字段取值
GaussDB(for MySQL)对自增字段的赋值有以下几种方法：
# 表结构
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
- 不对自增字段赋值，数据库会自动将自增值填入字段中，AUTO_INCREMENT自增。
a. 插入数据。
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');
b. 查询表数据。
select * from animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
c. 查询表结构。
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自增。
a. 插入数据。
INSERT INTO animals (id,name) VALUES(0,'groundhog');
INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
b. 查询数据。
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)
c. 查询表结构。
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。
a. 插入数据。
INSERT INTO animals (id,name) VALUES(100,'rabbit');
b. 查询数据。
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)
c. 查询表结构。
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不变。
a. 插入数据。
INSERT INTO animals (id,name) VALUES(-50,'-middle');
b. 查询数据。
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)
c. 查询表结构。
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，执行 show create table ，自增属性未在表结构中显示。
创建表：
执行show create table xxx ，未显示自增属性AUTO_INCREMENT：
原因分析
经过排查，是因为参数“sql_mode”设置了NO_FIELD_OPTIONS属性。
sql_mode相关属性介绍：
- NO_FIELD_OPTIONS：不要在SHOW CREATE TABLE的输出中打印MySQL专用列选项。
- NO_KEY_OPTIONS：不要在SHOW CREATE TABLE的输出中打印MySQL专用索引选项。
- NO_TABLE_OPTIONS：不要在SHOW CREATE TABLE的输出中打印MySQL专用表选项（例如ENGINE）。
解决方案
将sql_mode的NO_FIELD_OPTIONS属性去掉即可。
空用户的危害
MySQL中是允许用户名为 **'' **的用户存在，本章节介绍数据库中存在这种空用户时的危害。
MySQL中使用空用户时，它将可以匹配任何用户名。这一特性也会带来多种安全性、功能性危害。所以，在实际使用过程中应避免使用空用户。
- 安全性危害
- 当存在空用户时，连接时可以使用任意用户名进行登录。
- 如果空用户有密码，则使用任意用户名和空用户的密码即可登录数据库，并获得空用户所拥有的所有权限。示例：
#没有空用户时，使用非法用户名‘abcd’，连接失败
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
mysql -uabcd -h127.0.0.1 -P3306 -pTest_1234
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'abcd'@'localhost' (using password: YES)
# 创建空用户后，使用非法用户名‘abcd’，密码用空用户的密码，连接成功
mysql> create user ''@'localhost' IDENTIFIED BY 'Test_1234';
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
mysql -uabcd -h127.0.0.1 -P3306 -pTest_1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37Server version: 8.0.22-debug Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- 如果空用户没有密码，则使用任意用户名即可免密登录数据库，并获得空用户所拥有的所有权限。示例：
#存在无密码的空用户时，可以使用任意用户免密登录数据库。
mysql> create user ''@'localhost';
Query OK, 0 rows affected (8.87 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
mysql -uabcd -h127.0.0.1 -P3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39Server version: 8.0.22-debug Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates.
All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
#-----------------
mysql -usdhsjkdshk -h127.0.0.1 -P3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40Server version: 8.0.22-debug Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- 功能性危害
当存在空用户时，可能因为匹配出错，导致正常的用户名无法登录。
示例：存在空用户与root用户的host有重叠时，导致root用户无法使用密码登录，或者使用空用户的密码登录后无法进入root的权限。
mysql> create user ''@'localhost';
Query OK, 0 rows affected (8.87 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
# 用root的密码无法登录
mysql -uroot -h127.0.0.1 -P3306 -pTest_root
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
# 用空用户的密码(免密)登录后实际是空用户登录，没有root权限。
mysql -uroot -h127.0.0.1 -P3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 45Server version: 8.0.22-debug Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user,host from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'
慢日志显示SQL语句扫描行数为0
场景描述
查询慢日志中记录SQL执行65秒，但是扫描行数为0。
原因分析
被中断的查询超过慢日志设置阈值也会记录慢日志，但是所记录的扫描行数为0。客户JDBC连接设置了查询超时：
解决方案
优化SQL或者将sockTimeOut设置合理值。
