共計 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 行業資訊頻道！
