这篇文章主要为大家展示了“MySQL中的分区表和临时表是什么”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL中的分区表和临时表是什么”这篇文章吧。

MySQL中有一种表叫做临时表，指的是用CREATE TEMPORARY TABLE语句创建的，它是一种特殊类型的表，它允许存储临时结果，可以在单个会话中多次重复使用，对其他连接是不可见的，当连接中断后，数据表就会丢失，但也可以使用DROP TABLE在不需要它的情况下显式删除。

CREATETEMPORARYTABLEtable_name(column_1_definition,column_2_definition,....);

如果想要创建一个与现有表结构相同的临时表，使用CREATE TEMPORARY TABLE语句那就太麻烦了，可以使用下面语句

CREATETEMPORARYTABLEtemp_table_nameSELECT*FROMtable_nameLIMIT0;

还有一个特点是，临时表可以与其他表具有相同的名称，例如即使数据库中存在user的表，但也可以在数据库中创建user的临时表。

创建临时表示例

创建一个名为tblemployee的新临时表，此时使用SHOW TABLES是无法看到这张表的。

createtemporarytabletblemployee(idintauto_incrementPrimarykey,emp_namevarchar(500),emp_addressvarchar(500),emp_dept_idint)

向其插入数据。

mysql>insertintotblemployeevalues(1,'张三','北京',2);QueryOK,1rowaffected(0.00sec)mysql>select*fromtblemployee;+----+----------+-------------+-------------+|id|emp_name|emp_address|emp_dept_id|+----+----------+-------------+-------------+|1|张三|北京|2|+----+----------+-------------+-------------+1rowinset(0.01sec)mysql>

基于现有表结构创建

首先创建两个表。

createtabletb_user(user_namevarchar(255),user_idint(11));inserttb_uservalues("张三",1);inserttb_uservalues("李四",2);inserttb_uservalues("王五",3);createtablebalance(user_idint(11),balancedecimal(5,2));insertbalancevalues(1,200);insertbalancevalues(2,150);insertbalancevalues(3,100);

创建一个具有姓名和余额的临时表

createtemporarytabletemp_user_balanceselectuser_name,balancefromtb_userleftjoinbalanceontb_user.user_id=balance.user_id;

查看临时表中数据。

mysql>select*fromtemp_user_balance;+-----------+---------+|user_name|balance|+-----------+---------+|张三|200.00||李四|150.00||王五|100.00|+-----------+---------+3rowsinset(0.00sec)

但是当其他会话查看这个表时，会报错。

mysql>select*fromtemp_user_balance;ERROR1146(42S02):Table'test.temp_user_balance'doesn'texistmysql>

删除临时表

DROPTEMPORARYTABLEtable_name;分区表

MySQL在5.1的时候开始支持分区功能，分区指的是根据一定规则，把同一张表中不同行的记录分配到不同的物理文件中，每个区都是独立的，可以独立处理，也可以作为表的一部分处理，分区对应用来说是透明的，不会影响业务。

MySQL只支持水平分区，不支持垂直分区，水平分区是将同一张表不同行的记录分配到不同的物理文件中，而垂直分区指将同一张表的不同列记录分配到不同的物理文件中。

可以通过SHOW PLUGINS命令来查看MySQL是否启用了分区功能。

MySQL在创建分区的时候使用partition by语句定义每个分区存放的数据，在查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询的时候就不用扫描所有分区，提高效率。

分区类型

RANGE分区

他是一种基于一个连续区间范围，把数据分配到不同的分区，是最常用的一种分区类型，下面创建一个以id列区间的分区表。

createtableuser(idint(11),user_namevarchar(255))partitionbyrange(id)(partitionuser0valueslessthan(100),partitionuser1valueslessthan(200));

创建这个表后，表不再由一个ibd组成，而是由建立时候各个分区的ibd组成，可以先通过下面语句查看data目录位置，然后查看被分区后创建的ibd。

showglobalvariableslike"%datadir%"+---------------+-----------------+|Variable_name|Value|+---------------+-----------------+|datadir|/var/lib/mysql/|+---------------+-----------------+1rowinset(0.00sec)

root@hxl-PC:/var/lib/mysql/test#lsuser#p#user0.ibduser#p#user1.ibdroot@hxl-PC:/var/lib/mysql/test#

然后我们向里面插入3条数据，但是可以看到第三条id为250的却报错了，原因是要插入一个在分区没有定义的值，MySQL则抛出异常。

mysql>insertuservalues(50,"张三");QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(150,"张三");QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(250,"张三");ERROR1526(HY000):Tablehasnopartitionforvalue250mysql>

解决办法是添加一个MAXXXVALUE值的分区，让大于200的值全存放在这里面，这下就可以插入大于200的值了。

altertableuseraddpartition(partitionuser3valueslessthanmaxvalue);mysql>insertuservalues(250,"张三");QueryOK,1rowaffected(0.02sec)

可以通过下面语句查询PARTITIONS表下得到每个分区具体信息。

select*frominformation_schema.partitionswheretable_schema=database()andtable_name='user'\G;

由于现在三个分区，所以会出现三个row，每个row中的TABLE_ROWS表示存放的数量，故现在都是1，还有PARTITION_METHOD表示分区类型。

.....***************************1.row***************************PARTITION_METHOD:RANGESUBPARTITION_METHOD:NULLPARTITION_EXPRESSION:`id`SUBPARTITION_EXPRESSION:NULLPARTITION_DESCRIPTION:100TABLE_ROWS:1.....

也可以使用explain 查看查询时候使用了哪个分区。

LIST分区

LIST分区类似于RANGE，只是分区列的值只能存放特定的，就是一个枚举列表的值的集合。而RANGE是连续区间值的集合

createtableuser(idint(11))partitionbylist(id)(partitionuser0valuesin(1,3,5,7,9),partitionuser1valuesin(0,2,4,6,8));

同样插入一些数据，可以看到插入10的时候抛出异常，原因也是插入的数据不再分区定义中。

mysql>insertuservalues(1);QueryOK,1rowaffected(0.02sec)mysql>insertuservalues(2);QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(6);QueryOK,1rowaffected(0.02sec)mysql>insertuservalues(9);QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(10);ERROR1526(HY000):Tablehasnopartitionforvalue10mysql>

而其余1、2、6、9在user0、user1两个分区中各两条。

HASH分区

HASH的目的是将数据均匀的分布到定义的各个分区中，保证各个分区的数据量大致都是一样的， HASH分区没有向RANGE和LIST一样必须规定某个值在哪个分区中保存，HASH分区是自动完成的，我们只需要指定分区数量即可。

createtableuser(idint(11))partitionbyhash(id)partitions4;

那如何得知这个数据在哪个分区中存储呢，拿500来说，就是通过mod(500,分区数量)来得到，所以500在第1个分区中。

mysql>selectmod(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中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

createtableuser(idint(11))partitionbykey(id)partitions4;

但是我并没有找到关于详细分区介绍的算法，而是看到一些说通过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列不支持。

createtableuser(aint,bint)partitionbyrangecolumns(a,b)(partitionp0valueslessthan(5,12),partitionp1valueslessthan(maxvalue,maxvalue));

现在插入一些数据

insertintouser(a,b)values(4,11);insertintouser(a,b)values(6,13);

第一条由于(4,11) < (5,12) 所以在p0分区，而(6,13) < (5,12) ，超出预期，在p1分区。

子分区

子分区也称为复合分区，可以对分区表RANGE和LIST上分区再进分区。

createtableuser(idint,purchaseddate)partitionbyrange(year(purchased))subpartitionbyhash(to_days(purchased))subpartitions2(partitionp0valueslessthan(1990),partitionp1valueslessthan(2000),partitionp2valueslessthanmaxvalue);对NULL的处理

MySQL可以在分区键上使用NULL，会把他当做最小分区来处理，也就是会存放到第一个分区，但是在List分区中，NULL值必须定义在列表中，否则不能被插入。

以上是“MySQL中的分区表和临时表是什么”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！