A temporary table
A TABLE is a special type of TABLE that allows you to store TEMPORARY results. It can be used multiple times in a single session and is invisible to other connections. If a connection is broken, the TABLE will be lost. But you can also use DROP TABLE to explicitly delete it when it is not needed.
CREATE TEMPORARY TABLEtable_name( column_1_definition, column_2_definition, .... ) ;Copy the codeIf you want to CREATE a TEMPORARY TABLE with the same structure as an existing TABLE, using the CREATE TEMPORARY TABLE statement is too cumbersome
CREATE TEMPORARY TABLE temp_table_name SELECT * FROM table_name LIMIT 0;
Copy the codeAnother feature is that temporary tables can have the same name as other tables, for example, temporary tables of user can be created in the database even if a table of User exists in the database.
Create temporary representations
Create a new temporary table named tblemployee, which is not visible using SHOW TABLES.
create temporary table tblemployee
(
id int auto_increment Primary key,
emp_name varchar(500),
emp_address varchar(500),
emp_dept_id int
)
Copy the codeInsert data into it.
mysql> insert into tblemployee values(1.'Joe'.'Beijing'.2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tblemployee;
+----+----------+-------------+-------------+
| id | emp_name | emp_address | emp_dept_id |
+----+----------+-------------+-------------+
| 1 |Zhang SAN|Beijing| 2 |
+----+----------+-------------+-------------+
1 row in set (0.01 sec)
mysql>
Copy the codeCreate based on existing table structures
Start by creating two tables.
create table tb_user(user_name varchar(255),user_id int(11));
insert tb_user values(" zhang ",1);
insert tb_user values(" bill ",2);
insert tb_user values(" detective ",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);
Copy the codeCreate a temporary table with names and balances
create temporary table temp_user_balance select user_name,balance from tb_user left join balance on tb_user.user_id=balance.user_id;
Copy the codeView data in temporary tables.
mysql> select * from temp_user_balance;
+-----------+---------+
| user_name | balance |
+-----------+---------+
|Zhang SAN| 200.00 |
|Li si| 150.00 |
|Cathy| 100.00 |
+-----------+---------+
3 rows in set (0.00 sec)
Copy the codeHowever, when other sessions view the table, an error is reported.
mysql> select * from temp_user_balance;
ERROR 1146 (42S02): Table 'test.temp_user_balance' doesn't exist
mysql>
Copy the codeDelete temporary table
DROP TEMPORARY TABLE table_name;
Copy the codeThe partition table
MySQL started to support partitioning in 5.1. Partitioning refers to allocating records of different rows in the same table to different physical files according to certain rules. Each partition is independent and can be processed independently or as part of a table.
MySQL supports only horizontal partitioning, but does not support vertical partitioning. Horizontal partitioning means that the records of different rows of the same table are allocated to different physical files. Vertical partitioning means that the records of different columns of the same table are allocated to different physical files.
You can run the SHOW PLUGINS command to check whether MySQL has enabled partitioning.
When creating a partition, MySQL uses partition BY statement to define the data stored in each partition. When querying, the optimizer will filter the partition that does not have the required data according to the partition definition. In this way, it does not need to scan all partitions during querying, which improves efficiency.
Partition type
RANGE partitioning
Partition table (id) partition table (id) partition table (id) partition table (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));
Copy the codeAfter the table is created, the table is not composed of a single IBD, but the IBDS of each partition at the time of creation. You can view the location of the data directory first by using the following statement, and then view the IBDs created after partition.
show global variables like "%datadir%"
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
Copy the coderoot@hxl-PC:/var/lib/mysql/test# ls
user#p#user0.ibd user#p#user1.ibd
root@hxl-PC:/var/lib/mysql/test#
Copy the codeInsert (id 250); insert (id 250); insert (id 250);
mysql> insert user values(50, "zhang"); Query OK,1 row affected (0.01 sec)
mysql> insert user values(150, "zhang"); Query OK,1 row affected (0.01 sec)
mysql> insert user values(250, "zhang"); ERROR1526 (HY000): Table has no partition for value 250
mysql>
Copy the codeThe solution is to add a partition with MAXXXVALUE, where all values greater than 200 are stored, and then insert values greater than 200.
alter table user add partition (partition user3 values less than maxvalue);
mysql> insert user values(250, "zhang"); Query OK,1 row affected (0.02 sec)
Copy the codeYou can query the PARTITIONS table to obtain the details of each partition.
select * from information_schema.partitions where table_schema=database() and table_name='user'\G;
Copy the codeSince there are three partitions, there will be three rows. The TABLE_ROWS in each row represents the number of partitions, so it is now 1, and the PARTITION_METHOD represents the partition type.
.*************************** 1. row ***************************
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: `id`
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 100
TABLE_ROWS: 1.Copy the codeYou can also use Explain to see which partition was used for the query.
The LIST partition
A LIST partition is similar to a RANGE, except that the values of the partitioned column can only hold a specific set of values, that is, an enumerated LIST. And RANGE is a collection of continuous interval values
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));Copy the codeAlso insert some data, you can see that insert 10 throws an exception, because the inserted data is not in the partition definition.
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>
Copy the codeThe other 1, 2, 6, and 9 partitions are in user0 and user1 respectively.
HASH partitioning
The purpose of HASH is to evenly distribute data to all defined partitions to ensure that the amount of data in each partition is roughly the same. Unlike RANGE and LIST, HASH partitioning does not have to specify which partition to store a value in. HASH partitioning is automatic, so you only need to specify the number of partitions.
create table user (id int(11)) partition by hash(id) partitions 4;
Copy the codeHow do you know in which partition this data is stored? For example, 500 is obtained by mod(500, number of partitions), so 500 is in the first partition.
mysql> select mod(500.4)
-> ;
+------------+
| mod(500.4) |
+------------+
| 0 |
+------------+
Copy the codeFor example, at 31, mod(31,4) would be 3, so in the fourth partition, if both numbers are inserted and checked by information_schema.partitions, TABLE_ROWS in partition 1 and partition 4 would be 1.
For example, if you are looking for an equal number, you first calculate which partition the value should be in, and then perform the search. If you use < and > for range lookup, you use all partitions.
There are also some functions and other valid expressions for HASH, such as partition by HASH (ABS (ID)). However, not all functions can be used.
The KEY partition
A Key partition is similar to a HASH partition except that a HASH partition allows user-defined expressions. A Key partition does not allow user-defined expressions and requires the HASH function
The KEY partition allows multiple columns, while the HASH partition allows only one column. In addition, if there is a primary KEY or unique KEY, the partition column in the KEY can not be specified. By default, the primary KEY or unique KEY must be specified explicitly.
create table user(id int(11)) partition by key(id) partitions 4;
Copy the codeHowever, I did not find the algorithm introduced on partitioning in detail, but saw some operations through PASSWORD, which I did not understand.
The Columns partition
Columns was the type of partition introduced in 5.5. Prior to this, the RANGE and LIST partitions could only support integer partitions, requiring additional functions to compute. Columns solved this problem.
The Columns partition can be subdivided into RANGE Columns and LIST Columns. The supported types are as follows:
-
TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT, but no DECIMAL or FLOAT is supported.
-
The DATE and DATETIME.
-
CHAR, VARCHAR, BINARY, and VARBINARY, TEXT, and BLOB columns are not supported.
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)
);
Copy the codeNow insert some data
insert into user (a,b) values (4.11);
insert into user (a,b) values (6.13);
Copy the codeThe first is in p0 because (4,11) < (5,12), while (6,13) < (5,12), more than expected, is in p1.
The child partition
Subpartitions, also known as compound partitions, can be subpartitioned into partitions in the RANGE and LIST partition tables.
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
);
Copy the codeHandling of NULL
MySQL can use NULL values on partition keys and will treat them as the smallest partition, i.e. stored in the first partition, but in List partitions, NULL values must be defined in the List, otherwise they cannot be inserted.