# `HASH` Partition - Example

+ `HASH` partition table ensures an even distribution of data among partitions
+ The partition key is of integer type

```
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 HASH(MONTH(ulogin))@!~
~!@PARTITIONS 12@!~;
```

------------------------------------------------------------------------------
A `HASH` partitioned table is primarily utilized to evenly distribute data among a specified number of partitions, based on a user-supplied expression during table creation. You can achieve this distribution by specifying the keyword `PARTITIONS` followed by the desired number of partitions.

To create a `HASH` partitioned table, you must add a `PARTITION BY HASH (expr)` clause to the `CREATE TABLE` statement. The `expr` should be an integer-returning expression, which can be a column name if it has an integer data type. Furthermore, you may need to include `PARTITIONS num`, where `num` is a positive integer specifying the number of partitions the table will be divided into.

```
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 HASH(MONTH(ulogin))
 -> PARTITIONS 12;
Query OK, 0 rows affected (0.54 sec)
```

When using `PARTITION BY HASH`, TiDB decides which partition the data should fall into based on the modulus of the result of the expression. In other words, if a partitioning expression is `expr` and the number of partitions is `num`, `MOD(expr, num)` decides the partition in which the data is stored. In the above example, the table `users` stores data in 12 different partitions based on the modulus operation with the month. If we have a data record with `ulogin` as `2023-11-21`, then according to `MOD(expr, num)`, this row of data should be inserted into the 11th partition. 

The most efficient hash function operates on a single table column and its value should consistently scale with the column value. For instance, if the data is predominantly generated in the year 2024, like `2024-02-15` and `2024-06-09`, but the hash partitioning expression is based on the function `YEAR()`, all the data will likely end up in a single partition, negating the purpose of data distribution.

TiDB supports parsing the MySQL syntax `PARTITION BY LINEAR HASH` but disregards the `LINEAR` keyword within it. For example,

```
CREATE TABLE employees (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30),
 hired DATE NOT NULL DEFAULT '1970-01-01',
 separated DATE NOT NULL DEFAULT '9999-12-31',
 job_code INT,
 store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;
```

When partitioning tables by `HASH`, there is a different way of handling `NULL` value. If the calculation result of the partitioning expression is `NULL`, it is considered as 0.

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