mysql的开发常用

数据类型以及选择如何数据类型：

1、为表中的字段选择合适的数据类型

• 当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。

• 对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

2、如何选择正确的整数类型

类型	大小	范围（SIGNED）	范围（UNSIGNED）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值，如年龄
SMALLINT	2 字节	(-32768，32 767)	(0，65535)	大整数值
MEDIUMINT	3 字节	(-8388608，8388607)	(0，16777 215)	大整数值
INT	4 字节	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8 字节	(-9233372036854775808，9223372036854775807)	(0，18446744073709551615)	极大整数值

3、如何选择正确的实数类型

列类型	存储空间	是否精确
FLOAT	4个字节	否
DOUBLE	8个字节	否
DECIMAL（与金额有关的）	每4个字节存9个数字，小数点占一个字节	否

• DECIMAL(18,9)：表示该字段可存储数字最大的长度是18位，其中小数位最大的长度是9位。由于DECIMAL每4个字节存9个数字，小数点占一个字节，所以该字段占9个字节。

4、如何选择VARCHAR和CHAR类型

• VARCHAR和CHAR类型长度是以字符为定位的而不是字节，如UTF-8编码，每个字符占3个字节，那么CHAR(10)就需要占30个字节

• VARCHAR类型的存储特点

  • VARCHAR用于存储变长字符串，只占用必要的存储空间。

  • 当列的最大长度小于255时，只占用一个额外字节用于记录字符串长度。

  • 当列的最大长度大于255，则需要占用两个额外字节用于记录字符串长度。

• VARCHAR长度选择问题

  • 使用最小的符合需求的长度。

  • 系统上线后尽量不要修改VARCHAR的长度，因为在mysql 5.7之前，只要一修改就会发生锁表。

  • VARCHAR(5)和VARCHAR(200)存储'AAAAA'这一个字符串时都是使用一个额外的字节来记录字符串的长度，那么他们的性能有什么不同呢？

    • VARCHAR(5)优于VARCHAR(200)

    • 因为MySQL为了能够更有效的优化查询，对于VARCHAR字段使用的是其最大的宽度来分配内存，所以如果我们把宽度定的太长就会消耗更多的内存。

• VARCHAR类型的适用场景

  • 字符串列的最大长度比平均长度大很多

  • 字符串列很少被更新

  • 使用了多字节字符集存储字符串（如utf8中的中文和英文）

•  

  CHAR类型的存储特点

  • CHAR类型是定长的

  • 字符串存储在CHAR类型的列中会删除末尾的空格（VARCHAR则不会）

  • CHAR类型的最大宽度是255个字符，所以如果当我们长度超过255时，则需要使用VARCHAR类型进行存储

• CHAR类型的适用场景

  • 存储长度近似的值（md5值，如密码）

  • 存储短字符串

  • 存储经常被更新的字符串列

5、如何选择日期类型

• DATATIME类型

  • 以yyyy-MM-dd HH:mm:ss[.fraction]格式存储日期时间

    • DATATIME = yyyy-MM-dd HH:mm:ss
    • DATATIME(6) = yyyy-MM-dd HH:mm:ss.fraction

  • 与时区无关，占8个字节的存储空间

  • 时间范围：1000-1-1 00:00:00至9999-12-31 23:59:59

• TIMESTAMP类型

  • 存储了由格林尼治时间1970-1-1 00:00:00到当前时间的秒数，以yyyy-MM-dd HH:mm:ss[.fraction]的格式显示，占用4个字节

  • 时间范围：1970-1-1 到 2038-01-19

  • 显示依赖于所指定的时区

  • 在行的数据修改时可以自动修改TIMESTAMP列的值（设置为NOT NULL即可），默认是第一个为TIMESTAMP类型的列才会自动更新

• DATE类型（如生日）

  • 占用3个字节

  • 使用DATE类型可以利用日期函数进行日期之间的计算

  • 范围：1000-01-01到9999-12-31

• TIME类型

  • HH:MM:SS

• 注意事项：

  • 不要使用字符串类型存储日期时间数据

    • 日期时间类型通常比字符串占用空间小

    • 日期类型在进行查找过滤时可以利用日期来进行对比

    • 可以利用日期函数进行日期之间的计算

  • 使用Int存储日期时间不如使用TIMESTAMP类型

    • 因为TIMESTAMP类型底层其实也是使用INT来存储的，只不过显示的时候格式化了而已，但更方便查看