# `VERSION::7.1.0` `KEY` Partition - Example

+ **`KEY`** partition is similar to **`HASH`** partition
+ **`KEY`** partitioning supports distributing data based on a column list
+ The data types of partition columns can be integer, string (**`CHAR`** or **`VARCHAR`**), **`DATE`**, and **`DATETIME`**
+ Expressions, such as **`YEAR()`**, are not supported

```
CREATE TABLE users (
 uid INT NOT NULL,
 uname VARCHAR(25) NOT NULL,
 ucity VARCHAR(25) NOT NULL,
 addr VARCHAR(25) NOT NULL,
 ulogin DATE NOT NULL DEFAULT CURRENT_DATE()
)
~!@PARTITION BY KEY(uid, ucity)@!~
PARTITIONS 10;
```

------------------------------------------------------------------------------
Both `KEY` partitioning and `HASH` partitioning can evenly distribute data across a specified number of partitions. However, the key distinction lies in the fact that while `HASH` partitioning exclusively enables data distribution based on a specified integer expression or an integer-typed column, `KEY` partitioning allows data distribution based on a list of columns, with no restriction on the column types for partitioning.

To create a `KEY` partitioned table, you need to append a `PARTITION BY KEY (colum_list)` clause to the `CREATE TABLE` statement. `colum_list` is a column list with one or more column names. The data type of each column in the list can be any type except `BLOB`, `JSON`, and `GEOMETRY` (Note that TiDB does not support `GEOMETRY`). In addition, you might also need to append `PARTITIONS num` (where `num` is a positive integer indicating how many partitions a table is divided into), or append the definition of the partition names. For example:
```
tidb> CREATE TABLE users (
 -> uid INT NOT NULL,
 -> uname VARCHAR(25) NOT NULL,
 -> ucity VARCHAR(25) NOT NULL,
 -> addr VARCHAR(25) NOT NULL,
 -> ulogin DATE NOT NULL DEFAULT CURRENT_DATE()
 -> )
 -> PARTITION BY KEY(uid, ucity)
 -> PARTITIONS 10;
Query OK, 0 rows affected (0.54 sec)
```

If `PARTITIONS num` is not specified, the default number of partitions is 1. If the value of a partitioning field is `NULL`, it is treated as 0. 

Currently, TiDB does not support creating `KEY` partitioned tables if the partition column list specified in `PARTITION BY KEY` is empty. For example, after you execute the following statement, TiDB will create a non-partitioned table and return an `Unsupported partition type KEY`, treat as normal table warning.

TiDB supports parsing the MySQL `PARTITION BY LINEAR KEY` syntax for `KEY` partitioning. However, TiDB ignores the `LINEAR` keyword and uses a non-linear hash algorithm instead.

```
tidb> SELECT TABLE_NAME, GROUP_CONCAT(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='users' GROUP BY TABLE_NAME;
+------------+------------------------------+
| TABLE_NAME | GROUP_CONCAT(PARTITION_NAME) |
+------------+------------------------------+
| users      | p0,p1,p2,p3                  |
+------------+------------------------------+
1 row in set (0.02 sec)
```

More details: `https://docs.pingcap.com/tidb/stable/partitioned-table#hash-partitioning`.