〇、MySQL数据类型
1. 数值数据类型
数值数据类型存储数值。
MySQL支持多种数值数据类型,每种存储的数值具有不同的取值范围。
整数
- 长度
int(n)
与zerofill
int(n)
只影响显示字符的宽度,不限制数值的合法范围。int(3)
依然可以存储 123456789
这么大的数值。
若设置了 zerofill
属性,当 int(3)
存储 12
时,会在前面补0,补足3位。即 012
;当 int(5)
存储 12
时,会在前面补三个0,补足5位。即 00012
- 有符号或无符号
所有数值数据类型(除 BIT
和 BOOLEAN
外)都可以有符号或无符号。有符号数值列可以存储正或负的数值,无符号数值列只能存储正数。默认情况为有符号,但如果你知道自己不需要存储负值,可以使用 UNSIGNED
关键字,这样做将允许你存储两倍大小的值。
小数
DECIMAL最常用的用法就是用来存储货币,例如DECIMAL(8, 2)
。
DECIMAL还可以用于存储比BIGINT还大的整数以及精确的小数。
2. 串数据类型
从 MySQL4.1 版本开始,char(n)
和varchar(n)
中的n
指字符长度,不再表示之前版本的字节长度。也就是说在不同字符集下,char类型列的内部存储可能不是定长数据。
CHAR
CHAR 是定长字符串,会直接根据定义字符串时指定的长度分配足够的空间。
CHAR 适合存储所有值长度相同的字符串或很短的字符串。
VARCHAR
VARCHAR 的最大长度是65535个字节,而 varchar(n)
中的 n
指字符长度,因此,n
的最大值是由当前字段的字符集决定的。当字符集是 utf8
时,n
的最大值为 21845。当字符集是 utf8mb4
时,n
的最大值为 16383。(但是实际上MySQL要求一个行的定义长度不能超过65535个字节,因此,除非表中只有这一个字段,否则 n
的值达不到上述的最大值)。
VARCHAR 使用1-2个额外字节记录字符串长度,列长度小于等于255个字符时,使用1个字节记录,否则使用2个字节。
最佳实践
- 对于经常变更的数据, CHAR 比 VARCHAR 更好,CHAR 的磁盘空间利用率更高,不容易产生碎片。
- 当列中数据的长度相同时,选择 CHAR;当列中数据长度参差不齐时,选择 VARCHAR。
- 对于非常短的列,CHAR 比 VARCHAR 在存储上更有效率。
- 只分配真正需要的空间,更长的列会消耗更多的内存。
- 尽量避免使用 BLOB/TEXT 类型,查询时会使用临时表,导致严重的性能开销。如果一定要用,建议单独建表存储该字段。
3. 二进制数据类型
4. 日期和时间类型
最佳实践
- 尽量使用 TIMESTAMP,比 DATETIME 的空间利用率高。
一、创建数据表
CREATE TABLE
使用CREATE TABLE 创建表,必须给出下列信息:
- 表的名字,在关键字 CREATE TABLE 之后给出;
- 表中字段的名字和定义,用逗号分隔。
以下为创建MySQL数据表的SQL通用语法:
CREATE TABLE table_name (
column1 datatype [NULL|NOT NULL] [DEFAULT ],
column2 datatype,
);
实例
- 创建用户表
CREATE TABLE IF NOT EXISTS `user` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '密码',
`status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
`created_at` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`) US