MySQL库表

1. 表的概述

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

2. 数据库操作

2.1 查询所有数据库：

sql

show databases;

2.2 查询当前数据库

sql

select database();

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. 分区表

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

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;

6. 主键

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

6.1 主键生成方法

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

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

6.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 查询当前数据库 ​

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分区 ​

6. 主键 ​

6.1 主键生成方法 ​

6.2 操作生成唯一字符串 ​

6.3 分区表中生成全局ID ​