MySQL库表

1. 表的概述

表是关系数据库的核心，作为数据记录的集合。MySQL默认存储引擎都是基于行(记录)存储，每行记录都是基于列进行组织的

2. 数据库操作

2.1 查询所有数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| demo               |
| information_schema |
| kanban_test        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

2.2 查询当前数据库

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.01 sec)
mysql> use demo;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| demo       |
+------------+
1 row in set (0.00 sec)

2.3 创建数据库

sql

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ];

在同一个数据库服务器中，不能创建两个名称相同的数据库，否则将会报错。可以通过if not exists参数来解决这个问题，数据库不存在, 则创建该数据库，如果存在，则不创建。

sql

create database if not extists dm default charset utf8mb4;

2.4 删除数据库

sql

drop database [ if exists ] 数据库名 ;

2.5 切换数据库

sql

use 数据库名;

3. 表操作

3.1 查询当前数据库所有表

sql

show tables;

3.2 查看指定表结构

通过这条指令，我们可以查看到指定表的字段，字段的类型、是否可以为NULL，是否存在默认值等信息。

mysql> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(255) | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3.3 查询指定表的建表语句

通过这条指令，主要是用来查看建表语句的，而有部分参数我们在创建表的时候，并未指定也会查询到，因为这部分是数据库的默认值，如：存储引擎、字符集等。

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.4 查看表统计信息

mysql> show table status like 't1';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| t1   | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |           NULL | 2025-02-25 15:38:49 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

4. 表的元数据

表相关的元数据存放在information_schema库中，涉及到的表有TABLES，COLUMNS、PARTITIONS(如果表是分区表)。

4.1 TABLES表

mysql> select * from tables limit 1\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: mysql
     TABLE_NAME: columns_priv
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 4194304
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2024-12-02 17:38:40
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb3_bin
       CHECKSUM: NULL
 CREATE_OPTIONS: row_format=DYNAMIC stats_persistent=0
  TABLE_COMMENT: Column privileges
1 row in set (0.00 sec)

TABLES表可以得到表的引擎和大致表每行的占用字节数信息以及使用的字符集。比如编写巡检脚本查询不是InnoDB引擎的表：

sql

select 
table_schema,
table_name,
sys.format_bytes(data_length) as data_size
from 
tables
where (engine <> 'InnoDB' or TABLE_COLLATION not like 'utf8%')
and table_schema not in ('mysql', 'performance_schema', 'information_schema')

4.2 COLUMNS表

字段的元数据表。

5. 分区表

将一个表或者索引分解为多个更小、更可管理的部分，目前只支持水平分区。每个分区保存自己的数据与索引，分区列必须是唯一索引的一个组成部分。

5.1 Range分区

适用场景：当表中的数据可以按照连续的范围(如日期、ID、时间等)进行划分时，可采用范围分区。

sql

create table test.t_range(
id int PRIMARY KEY
) ENGINE =INNODB
PARTITION by range(id)(
partition p0 values less than (10),
PARTITION p1 values less than (20)
);

5.2 Hash分区

分区的列需要时整型。
适用场景：当需要将数据均匀分布到各个分区，以平衡I/O负载时，可考虑哈希分区。

sql

create table test.t_hash(
a int,
b datetime
) ENGINE=INNODB
PARTITION by hash(YEAR(b))
PARTITIONS 4;

5.3 List分区

适用场景：适用于数据可以依据离散的值集合(如地区、状态等)进行分组的情况。

sql

create table test.t_list(
a int,
b int
)ENGINE=INNODB
PARTITION by list(b)(
PARTITION p0 values in (1,3,5,7,9),
partition p1 values in (2,4,6,8)
);

5.4 Key分区

将分区列进行md5后再分区，和hash分区类似：
适用场景：与哈希分区类似，但键分区支持使用非整数列进行分区，且MySQL会自动处理哈希函数。

sql

create table test.t_key(
a int,
b datetime
)ENGINE=INNODB
partition by key(b)
partitions 4;

5.5 分区索引

在MySQL中，分区表上的索引默认是“局部”的, MySQL分区表索引默认为每个分区独立创建一棵B+Tree索引树，无法保证主键全局唯一性。

shell

mysql> CREATE TABLE Orders (
    ->   OrderId BIGINT AUTO_INCREMENT,
    ->   OrderDate Datetime,
    ->   PRIMARY KEY(OrderId)
    -> )
    -> PARTITION BY RANGE COLUMNS (OrderDate) (
    ->   PARTITION p0 VALUES LESS THAN ('2009-01-01'),
    ->   PARTITION p1 VALUES LESS THAN ('2009-02-01')
    -> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
## 调整主键为联合主键
mysql> CREATE TABLE Orders (
    ->   OrderId BIGINT AUTO_INCREMENT,
    ->   OrderDate Datetime,
    ->   PRIMARY KEY(OrderId, OrderDate)
    -> )
    -> PARTITION BY RANGE COLUMNS (OrderDate) (
    ->   PARTITION p0 VALUES LESS THAN ('2009-01-01'),
    ->   PARTITION p1 VALUES LESS THAN ('2009-02-01')
    -> );
Query OK, 0 rows affected (0.17 sec)

6. 临时表

6.1 创建临时表

mysql除了自身会在查询的使用在内存中创建临时表，也开放给用户可以手动创建临时表, 它是基于会话级别：

sql

create temporary table sql_trains.t_person(
    id int,
    name varchar(30)
);

也就意味着不同的用户可以创建相同的表，用户创建的临时表和mysql系统创建的表区别在于引擎不一样，mysql系统创建的临时表引擎是Memory: Alt text

shell

mysql> show variables like '%tmp%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_tmp_storage_engine      | InnoDB    |
| innodb_tmpdir                   |           |
| internal_tmp_mem_storage_engine | TempTable |
| replica_load_tmpdir             | /tmp      |
| slave_load_tmpdir               | /tmp      |
| tmp_table_size                  | 16777216  |
| tmpdir                          | /tmp      |
+---------------------------------+-----------+
7 rows in set (0.01 sec)

用户创建的临时表表结构默认存储在/tmp目录下的xxx.frm文件中，临时表数据默认存储在mysql数据目录下的ibtmp1文件下，它也就是临时表空间：
Alt text

6.2 外键约束

考虑到性能开销，一般用的比较少。 Alt text 在外键约束中可以设置不同的删除或更新行为：

RESTRICT：在试图删除或更新主表中的记录时，如果该记录在子表中被引用，则操作会被拒绝(失败)
CASCADE：在删除或更新主表中的记录时，相关的子表记录也会被自动删除或更新
SETNULL：当主表中的记录被删除或更新时，相应的子表中引用该记录的外键字段将被设置为NULL
NO ACTION：删除或更新主表记录时，如果有子表记录引用该主表记录，则操作失败，和RESTRICT类似

sql

create table product(
    category int not null ,
    id int not null,
    price DECIMAL,
    PRIMARY KEY(category, id)
) engine=INNODB;

create table customer(
    id int 	not null PRIMARY key
) engine=INNODB;

create table product_order(
    no int not null auto_increment,
    product_category int not null,
    product_id int not null,
    customer_id int not null,
    PRIMARY key(no),
    index(product_category, product_id),
    index(customer_id),
    FOREIGN KEY (product_category,product_id)
    REFERENCES product(category, id)
    on UPDATE cascade on delete RESTRICT,
    FOREIGN key (customer_id) 
    REFERENCES customer(id) 
) ENGINE =INNODB;

7. 主键

能够唯一表示数据表中的每个记录的字段或者字段的组合就称为主键。

7.1 主键生成方法

自增长，如identity,auto_increment
唯一编号
GUID(Global unique identifier)
应用程序主键生成器

7.2 操作生成唯一字符串

## 生成36位的
mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 39cc9a2f-12e9-11f0-a023-000c29b9fd34 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select hex(uuid());
+--------------------------------------------------------------------------+
| hex(uuid())                                                              |
+--------------------------------------------------------------------------+
| 34356531323936302D313265392D313166302D613032332D303030633239623966643334 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
## 生成32位的
mysql> select hex(uuid_to_bin(uuid()));
+----------------------------------+
| hex(uuid_to_bin(uuid()))         |
+----------------------------------+
| 6DC021BB12E911F0A023000C29B9FD34 |
+----------------------------------+
1 row in set (0.00 sec)
## mysql8.0支持16位的UUID存储，但是读取需要转换使用BIN_TO_UUID
mysql> select hex(uuid_to_bin(uuid()));
+----------------------------------+
| hex(uuid_to_bin(uuid()))         |
+----------------------------------+
| ED93F4AD959911F1915B6E6E1BB7C657 |
+----------------------------------+
1 row in set (0.00 sec)

7.3 分区表中生成全局ID

sql

create table test.t_order(
	order_id bigint auto_increment,
	create_time datetime,
	PRIMARY KEY(order_id, create_time)
)ENGINE=INNODB
PARTITION by range COLUMNS(create_time)(
partition p0 values less than ('2025-01-01'),
partition p1 values less than ('2025-02-01'),
partition p2 values less than ('2025-03-01'),
partition p3 values less than ('2025-04-01'),
partition p4 values less than ('2025-05-01')
);

分区表中的主键会在各个分区中唯一，如果需要全局唯一，解决办法：

修改主键类型为字符串，通过客户端或者程序中uuid函数生成
额外创建一张表：

sql

create table test.orderId(
	order_id bigint PRIMARY KEY auto_increment
);
-- 开启事务，保证原子性
begin;
insert into test.orderId VALUES(null);
insert into test.t_order values(LAST_INSERT_ID(), now());
commit;

8. 在线DDL支持

所谓在线DDL就是在执行表结构变更的过程中，尽量不阻塞对表的读写操作，从而减少对线上业务的影响。

操作	即时	原地	重建表	允许并发DML	仅修改元数据
添加列	是*	是	否*	是*	是
删除列	是*	是	是	是	是
重命名列	是*	是	否	是*	是
重新排序列	否	是	是	是	否
设置列默认值	是	是	否	是	是
更改列数据类型	否	否	是	否	否
扩展 VARCHAR 列长度	否	是	否	是	是
删除列默认值	是	是	否	是	是
更改自增值	否	是	否	是	否*
将列设为 NULL	否	是	是*	是	否
将列设为 NOT NULL	否	是*	是*	是	否
修改 ENUM 或 SET 列的定义	是	是	否	是	是

注：带 * 的条目表示该操作在相应算法下存在附加条件或限制（例如对存储引擎、列位置或版本有特定要求），具体请参考MySQL官方文档。

8.1 `innodb_online_alter_log_max_size`参数

这是MySQL InnoDB存储引擎的一个参数（标准写法为innodb_online_alter_log_max_size），用于控制在线DDL操作期间临时日志的最大大小。如果超过限制即使是在线DDL也会报错。

8.2 Percona-Toolkit

Percona-Toolkit‌是一套数据库管理命令行工具集。

下载安装
Percona-Toolkit‌里面提供包括实时变更表结构工具pt-online-schema-change, 从而避免锁表的可能：
pt-online-schema-change底层是使用触发器来实现的表变更，然后利用rename的原子性, 可以设置数据库操作记录从log文件变为表，然后查询日志表信息：

sql

-- 查看当前通用日志记录模式，
-- FILE表示文件方式，table表示记录表
show variables like '%log_output%';
-- 关闭日志记录
set global general_log = 0;
-- 设置全局，使得命令行工具使用也能够生效
set global log_output='table';
-- 清空日志表数据
truncate table mysql.general_log;
-- 开启日志记录
set global general_log = 1;

使用pt-online-schema-change工具对数据库的操作：

pt-online-schema-change --alter "convert to character utf8mb4" D=test, t=a --execute

可以发现流程如下：

先建表，按照指定的表结构创建a_new表。
创建触发器在触发器中使用了符号<=>,这是mysql独有的，表示等于null判断，和is null等效。
执行数据导入, 如果数据太多会执行分页插入

sql

INSERT LOW_PRIORITY IGNORE INTO `test`.`_a_new`(`a`, `z`)
SELECT * FROM `test`.`a` LOCK SHARE MODE

使用IGNORE是避免前面因为触发器实时数据插入后，在这里批量插入时忽略数据重复。使用LOCK SHARE MODE使用表锁防止a在被写入。
4. 原子性的修改表名

sql

RENAME TABLE `test`.`a` to `test`.`a_old`, `test`.`a_new` to `test`.`a`;

删除触发器

sql

DROP TABLE IF EXISTS `test`.`_a_old`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_a_del`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_a_upd`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_a_ins`;

pt-online-schema-change优点在于表结构修改在主从结构数据库延时很短，对生产的表影响较小，缺点是表必须有主键。

8.3 gh-ost工具

由GitHub开发，无触发器，通过异步解析binlog来同步增量数据。它的核心优势是对源库写入影响小，支持在运行时动态控制（如暂停、限流），并可将切换（Cut-over）安排到业务低峰期。不过，它需要Row-Based Replication (RBR)环境，并且不支持外键。

MySQL库表 ​

1. 表的概述 ​

2. 数据库操作 ​

2.1 查询所有数据库： ​

2.2 查询当前数据库 ​

2.3 创建数据库 ​

2.4 删除数据库 ​

2.5 切换数据库 ​

3. 表操作 ​

3.1 查询当前数据库所有表 ​

3.2 查看指定表结构 ​

3.3 查询指定表的建表语句 ​

3.4 查看表统计信息 ​

4. 表的元数据 ​

4.1 TABLES表 ​

4.2 COLUMNS表 ​

5. 分区表 ​

5.1 Range分区 ​

5.2 Hash分区 ​

5.3 List分区 ​

5.4 Key分区 ​

5.5 分区索引 ​

6. 临时表 ​

6.1 创建临时表 ​

6.2 外键约束 ​

7. 主键 ​

7.1 主键生成方法 ​

7.2 操作生成唯一字符串 ​

7.3 分区表中生成全局ID ​

8. 在线DDL支持 ​

8.1 innodb_online_alter_log_max_size参数 ​

8.2 Percona-Toolkit ​

8.3 gh-ost工具 ​