22、MySQL进阶：库表设计之IP和TIMESTAMP的处理

![](https://cloud.cxykk.com/images/2024/2/2/1541/1706859676721.png)

------------

### TIMESTAMP

我们使用 MySQL 内置的函数(FROM_UNIXTIME()，UNIX_TIMESTAMP())，可以将日期转化为数字，用 INT UNSIGNED 存储日期和时间

示例

时间2020-08-17 22:22:22 与整数之间的转换，**转化后数字是连续的，占用空间更小，并且可以使用索引提升查询性能。**
```python
mysql> select UNIX_TIMESTAMP('2020-08-17 22:22:22');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-08-17 22:22:22') |
+---------------------------------------+
|                            1597674142 |
+---------------------------------------+
1 row in set

mysql> select FROM_UNIXTIME(1597674142);
+---------------------------+
| FROM_UNIXTIME(1597674142) |
+---------------------------+
| 2020-08-17 22:22:22       |
+---------------------------+
1 row in set

mysql> 
```
```python
mysql> select UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
|            1597674191 |
+-----------------------+
1 row in set

mysql> select FROM_UNIXTIME(1597674191);
+---------------------------+
| FROM_UNIXTIME(1597674191) |
+---------------------------+
| 2020-08-17 22:23:11       |
+---------------------------+
1 row in set

mysql> 
```

![](https://cloud.cxykk.com/images/2024/2/2/1541/1706859682235.png)

------------

### IP

- 一般使用 Char(15) 进行存储，但是当进行查找和统计时，字符类型不是很高效。
- MySQL 数据库内置了两个 IP 相关的函数 INET_ATON()、INET_NTOA()，可以实现 IP 地址和整数的项目转换。

因此，我们使用 INT UNSIGNED（占用 4 个字节）存储 IP，非 Char(15)。占 15 个字节。
```python
mysql> select INET_ATON('127.0.0.1');
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set

mysql> select  INET_NTOA(2130706433);
+-----------------------+
| INET_NTOA(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set

mysql>

```
**将IP 的存储从字符型转换成整形，转化后数字是连续的，提高了查询性能，使查询更快，占用空间更小。**

![](https://cloud.cxykk.com/images/2024/2/2/1541/1706859688184.png)

------------

### 总结

1、 以高性能为目标，库表设计以范式为主，根据特殊业务场景使用反范式，允许必要的空间换时间；
2、 规范数据库的使用原则，统一规范命名，减少性能隐患，减少隐式转换；
3、 高性能表设计的原则：合适的字段、合适的长度、NOTNULL；
4、 从不同角度思考IP、timestamp的转换，拓宽设计思路；
5、 规范的命名可提高可读性，反范式设计可提高查询性能；