MySQL表

1. 表的概述

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

2. 创建表

2.1 建表语法

Alt text 表属性指定：

2.2 查看表结构

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)

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)

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)

3. 外键约束

考虑到性能开销，一般用的比较少。 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;

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. 修改表

修改表的语法如下：
Alt text 需要注意的是一下操作会造成表的操作阻塞，既不可写也不可读：
参考Online DDL Operations

6. 分区表

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

6.1 Range分区

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)
);

6.2 Hash分区

分区的列需要时整型。

sql

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

6.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)
);

6.4 Key分区

将分区列进行md5后再分区，和hash分区类似：

sql

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

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)

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;

MySQL表 ​

1. 表的概述 ​

2. 创建表 ​

2.1 建表语法 ​

2.2 查看表结构 ​

3. 外键约束 ​

4. 表的元数据 ​

4.1 TABLES表 ​

4.2 COLUMNS表 ​

5. 修改表 ​

6. 分区表 ​

6.1 Range分区 ​

6.2 Hash分区 ​

6.3 List分区 ​

6.4 Key分区 ​

7. 主键 ​

7.1 主键生成方法 ​

7.2 操作生成唯一字符串 ​

7.3 分区表中生成全局ID ​