共计 6212 个字符，预计需要花费 16 分钟才能阅读完成。
这篇文章主要为大家展示了“MySQL 中的分区表和临时表是什么”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让丸趣 TV 小编带领大家一起研究并学习一下“MySQL 中的分区表和临时表是什么”这篇文章吧。
临时表
MySQL 中有一种表叫做临时表，指的是用 CREATE TEMPORARY TABLE 语句创建的，它是一种特殊类型的表，它允许存储临时结果，可以在单个会话中多次重复使用，对其他连接是不可见的，当连接中断后，数据表就会丢失，但也可以使用 DROP TABLE 在不需要它的情况下显式删除。
CREATE TEMPORARY TABLE table_name(
column_1_definition,
column_2_definition,
);如果想要创建一个与现有表结构相同的临时表，使用 CREATE TEMPORARY TABLE 语句那就太麻烦了，可以使用下面语句
CREATE TEMPORARY TABLE temp_table_name SELECT * FROM table_name LIMIT 0;还有一个特点是，临时表可以与其他表具有相同的名称，例如即使数据库中存在 user 的表，但也可以在数据库中创建 user 的临时表。
创建临时表示例
创建一个名为 tblemployee 的新临时表，此时使用 SHOW TABLES 是无法看到这张表的。
create temporary table tblemployee
id int auto_increment Primary key,
emp_name varchar(500),
emp_address varchar(500),
emp_dept_id int
)向其插入数据。
mysql insert into tblemployee values(1, 张三 , 北京 ,2);
Query OK, 1 row affected (0.00 sec)
mysql select * from tblemployee;
+----+----------+-------------+-------------+
| id | emp_name | emp_address | emp_dept_id |
+----+----------+-------------+-------------+
| 1 | 张三 | 北京 | 2 |
+----+----------+-------------+-------------+
1 row in set (0.01 sec)
mysql基于现有表结构创建
首先创建两个表。
create table tb_user(user_name varchar(255),user_id int(11));
insert tb_user values(张三 ,1);
insert tb_user values(李四 ,2);
insert tb_user values(王五 ,3);
create table balance(user_id int(11),balance decimal(5,2));
insert balance values(1,200);
insert balance values(2,150);
insert balance values(3,100);创建一个具有姓名和余额的临时表
create temporary table temp_user_balance select user_name,balance from tb_user left join balance on tb_user.user_id=balance.user_id;查看临时表中数据。
mysql select * from temp_user_balance;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三 | 200.00 |
| 李四 | 150.00 |
| 王五 | 100.00 |
+-----------+---------+
3 rows in set (0.00 sec)但是当其他会话查看这个表时，会报错。
mysql select * from temp_user_balance;
ERROR 1146 (42S02): Table test.temp_user_balance doesn t exist
mysql删除临时表
DROP TEMPORARY TABLE table_name;分区表
MySQL 在 5.1 的时候开始支持分区功能，分区指的是根据一定规则，把同一张表中不同行的记录分配到不同的物理文件中，每个区都是独立的，可以独立处理，也可以作为表的一部分处理，分区对应用来说是透明的，不会影响业务。
MySQL 只支持水平分区，不支持垂直分区，水平分区是将同一张表不同行的记录分配到不同的物理文件中，而垂直分区指将同一张表的不同列记录分配到不同的物理文件中。
可以通过 SHOW PLUGINS 命令来查看 MySQL 是否启用了分区功能。
MySQL 在创建分区的时候使用 partition by 语句定义每个分区存放的数据，在查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询的时候就不用扫描所有分区，提高效率。
分区类型
RANGE 分区
他是一种基于一个连续区间范围，把数据分配到不同的分区，是最常用的一种分区类型，下面创建一个以 id 列区间的分区表。
create table user(id int(11),user_name varchar(255))
partition by range(id)(partition user0 values less than (100),
partition user1 values less than (200));创建这个表后，表不再由一个 ibd 组成，而是由建立时候各个分区的 ibd 组成，可以先通过下面语句查看 data 目录位置，然后查看被分区后创建的 ibd。
show global variables like %datadir%
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)root@hxl-PC:/var/lib/mysql/test# ls
user#p#user0.ibd user#p#user1.ibd
root@hxl-PC:/var/lib/mysql/test#然后我们向里面插入 3 条数据，但是可以看到第三条 id 为 250 的却报错了，原因是要插入一个在分区没有定义的值，MySQL 则抛出异常。
mysql insert user values(50, 张三
Query OK, 1 row affected (0.01 sec)
mysql insert user values(150, 张三
Query OK, 1 row affected (0.01 sec)
mysql insert user values(250, 张三
ERROR 1526 (HY000): Table has no partition for value 250
mysql解决办法是添加一个 MAXXXVALUE 值的分区，让大于 200 的值全存放在这里面，这下就可以插入大于 200 的值了。
alter table user add partition (partition user3 values less than maxvalue);
mysql insert user values(250, 张三
Query OK, 1 row affected (0.02 sec)可以通过下面语句查询 PARTITIONS 表下得到每个分区具体信息。
select * from information_schema.partitions where table_schema=database() and table_name= user由于现在三个分区，所以会出现三个 row，每个 row 中的 TABLE_ROWS 表示存放的数量，故现在都是 1，还有 PARTITION_METHOD 表示分区类型。
.....
*************************** 1. row ***************************
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: `id`
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 100
TABLE_ROWS: 1
.....也可以使用 explain 查看查询时候使用了哪个分区。
LIST 分区
LIST 分区类似于 RANGE，只是分区列的值只能存放特定的，就是一个枚举列表的值的集合。而 RANGE 是连续区间值的集合
create table user (id int(11))
partition by list(id)(partition user0 values in(1,3,5,7,9),
partition user1 values in(0,2,4,6,8)
);同样插入一些数据，可以看到插入 10 的时候抛出异常，原因也是插入的数据不再分区定义中。
mysql insert user values(1);
Query OK, 1 row affected (0.02 sec)
mysql insert user values(2);
Query OK, 1 row affected (0.01 sec)
mysql insert user values(6);
Query OK, 1 row affected (0.02 sec)
mysql insert user values(9);
Query OK, 1 row affected (0.01 sec)
mysql insert user values(10);
ERROR 1526 (HY000): Table has no partition for value 10
mysql而其余 1、2、6、9 在 user0、user1 两个分区中各两条。
HASH 分区
HASH 的目的是将数据均匀的分布到定义的各个分区中，保证各个分区的数据量大致都是一样的，HASH 分区没有向 RANGE 和 LIST 一样必须规定某个值在哪个分区中保存，HASH 分区是自动完成的，我们只需要指定分区数量即可。
create table user (id int(11)) partition by hash(id) partitions 4;那如何得知这个数据在哪个分区中存储呢，拿 500 来说，就是通过 mod(500, 分区数量) 来得到，所以 500 在第 1 个分区中。
mysql select mod(500,4)
- ;
+------------+
| mod(500,4) |
+------------+
| 0 |
+------------+在比如 31，那么 mod(31,4) 就是 3，所以在第 4 个分区中，如果把这两个数插入进去，通过 information_schema.partitions 查看，那么 1、4 分区的 TABLE_ROWS 都为 1。
通过条件查找数据时，使用到的分区也不一样，比如查找相等的数，那么首先计算这个值应该在哪个分区，然后在进行查找，如果使用、来范围查找，则会使用所有分区。
还有 HASH 可以使用一些函数或其他有效表达式，比如创建时可以使用 partition by hash(abs(id))，但并不是所有函数都可以使用，可使用的函数可以参考官网；
KEY 分区
Key 分区和 HASH 类似，不同的是，HASH 分区允许使用用户自定义的表达式，KEY 分区不允许使用用户自定义的表达式，需要使用 HASH 函数
KEY 分区允许多列，而 HASH 分区只允许一列，另外在有主键或者唯一键的情况下，key 中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
create table user(id int(11)) partition by key(id) partitions 4;但是我并没有找到关于详细分区介绍的算法，而是看到一些说通过 PASSWORD 的运算，没有搞懂。
Columns 分区
Columns 分区是 5.5 引入的分区类型，在此之前，RANGE 分区和 LIST 分区只能支持整数分区，从而需要额外的函数来计算，Columns 分区解决了这个问题。
Columns 分区可以细分为 RANGE Columns 和 LIST Columns 分区，支持的类型如下：
TINYINT、SMALLINT、MEDIUMINT、INT (INTEGER) 和 BIGINT，但是不支持 DECIMAL 或 FLOAT。
DATE 和 DATETIME。
CHAR，VARCHAR，BINARY，和 VARBINARY，TEXT 和 BLOB 列不支持。
create table user (
a int,
b int
partition by range columns(a, b) ( partition p0 values less than (5, 12),
partition p1 values less than (maxvalue, maxvalue)
);现在插入一些数据
insert into user (a,b) values (4,11);
insert into user (a,b) values (6,13);第一条由于 (4,11) (5,12) 所以在 p0 分区，而 (6,13) (5,12)，超出预期，在 p1 分区。
子分区
子分区也称为复合分区，可以对分区表 RANGE 和 LIST 上分区再进分区。
create table user (id int, purchased date)
partition by range( year(purchased) )
subpartition by hash( to_days(purchased) )
subpartitions 2 ( partition p0 values less than (1990),
partition p1 values less than (2000),
partition p2 values less than maxvalue
);对 NULL 的处理
MySQL 可以在分区键上使用 NULL，会把他当做最小分区来处理，也就是会存放到第一个分区，但是在 List 分区中，NULL 值必须定义在列表中，否则不能被插入。
以上是“MySQL 中的分区表和临时表是什么”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
