本文深入解析MySQL中的数据类型，涵盖整数类型、浮点数类型、日期和时间类型、字符串类型及二进制类型，同时介绍了复合类型以及字段属性如主键、唯一键、非空约束等关键概念。
向导
数据类型
数据类型是数据的一种属性，其可以决定数据的存储格式，有效范围和相应的限制。
mysql的数据类型分为五种，包括整数类型，浮点数类型，日期和时间类型，字符串类型和二进制类型。
数值类型
| 类型 | 大小 | 范围 | 无符号范围 | 用途 |
|---|---|---|---|---|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
不必多说，存储数字的类型，是专门用来存储数值的，根据实际情使用对应的数值类型，唯一要注意的就是满足要求的同时，尽量使用小字节的数据类型。
例如：创建一个表，用于统计公司人数，年龄
mysql> create table test(id smallint,age tinyint);
Query OK, 0 rows affected (0.04 sec)
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | smallint(6) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
对于以上需求，使用mediumint就可满足要求，但考虑类型大小，选择了最适合的数值类型。
浮点数类型
| 类型 | 大小 | 范围 | 无符号范围 | 用途 |
|---|---|---|---|---|
| FLOAT(M,D) | 4 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度，浮点数值 |
| DOUBLE(M,D) | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度，浮点数值 |
| DECIMAL(M,D) | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
浮点类型后面的(M,D)中填写的是数字，表示精度和标度。M指的是数字总个数，D指的是小数的数字个数。例如：float(5,2)表示总长度为5，有2位小数，123.456添加进去时，基于MySQL保存值四舍五入的机制，最后保存到数据库中的值为123.46。
精准度
float:单精度浮点型，占字节数为4，用32位二进制描述，有符号是7个有效位，无符号是8个有效位
double:双精度浮点型，占字节数为8，用64位二进制描述，有符号是15个有效位，无符号是16个有效位
decimal:数字型，用128位二进制描述，不存在精度损失，28个有效位。
计算机只能存储二进制，浮点数类型在存储的时候，必须转化成二进制。拿float型来说，如果将一个float型数值转化为二进制后，若转化的数据小于32位或32位后都是0，那么数据是准确的，如果32位后不全为0，则数据就会存在误差。
精准度比较，创建一个超过float精准度范围的表
mysql> create table test3(num1 float(10,2),num2 double(10,2),num3 decimal(10,2));
往里面添加内容
mysql> insert into test3 values(9876543.21,9876543.21,9876543.21);
查看表的数据
mysql> select * from test3;
+------------+------------+------------+
| num1 | num2 | num3 |
+------------+------------+------------+
| 9876543.00 | 9876543.21 | 9876543.21 |
+------------+------------+------------+
float型存储的数值二进制超过32位后的部分不能确保精准，结果小数部分就变成了0。
日期\时间类型
| 类型 | 大小 | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3字节 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3字节 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1字节 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8字节 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4字节 | 1970-01-01 00:00:00/2038，结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
对于时间和日期类型，就很简单，直接指定类型就可以了，连范围都不用加。
date类型使用
创建一个日期类型的表
mysql> create table test4(date date);
添加date型数据的两种方式
mysql> insert into test4 values('2020-2-23');
mysql> insert into test4 values(20200303);
查看记录是否成功
mysql> select * from test4;
+------------+
| date |
+------------+
| 2020-02-23 |
| 2020-03-03 |
+------------+
time类型使用
创建一个时间类型的表
mysql> create table test5(time time);
添加time型数据的三种方式
mysql> insert into test5 values('23:23:23');
mysql> insert into test5 values('23:23');
mysql> insert into test5 values('232323');
查看记录
mysql> select * from test5;
+----------+
| time |
+----------+
| 23:23:23 |
| 23:23:00 |
| 23:23:23 |
+----------+
year类型使用
创建一个年份类型的表
mysql> create table test6(year year);
添加一条记录
mysql> insert into test6 values(2020);
查看记录
mysql> select * from test6;
+------+
| year |
+------+
| 2020 |
+------+
datetime类型使用
范围1001年到9999年，精确到秒，使用8个字节存储。（建议目前使用timestamp类型）
创建一个日期加时间的表
mysql> create table test7(datetime datetime);
添加datetime型数据的两种方式，添加格式为：‘YYYY-MM-DD HH:MM:SS’
mysql> insert into test7 values('2020-2-22 23:23:23');
mysql> insert into test7 values(20200303222222);
查看记录
mysql> select * from test7;
+---------------------+
| datetime |
+---------------------+
| 2020-02-22 23:23:23 |
| 2020-03-03 22:22:22 |
+---------------------+
timestamp类型使用
范围从1970年1月1日以来的秒数，到达2038年1月19结束。和linux时间戳相同，用4个字节存储空间。
创建一个日期和时间的表
mysql> create table test8 (datetime timestamp);
添加timestamp型数据的两种方式
mysql> insert into test8 values('2020-11-11 11:11:11');
mysql> insert into test8 values(20201010063030);
查看记录是否添加成功
mysql> select * from test8;
+---------------------+
| datetime |
+---------------------+
| 2020-11-11 11:11:11 |
| 2020-10-10 06:30:30 |
+---------------------+
字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
根据需求和存储，灵活的使用数据类型，例如，存储名称和简介。
mysql> create table test9(name char(4),intro varchar(100));
添加一条记录
mysql> insert into test9 values('张三','简介');
查看记录
mysql> select * from test9;
+--------+--------+
| name | intro |
+--------+--------+
| 张三 | 简介 |
+--------+--------+
char和varchar区别
char定义的长度是固定的，取值为0~255之间，若插入的数据不足，将使用空格填充，直到达到范围。
varchar定义的长度是可变长字符串，取值为0~65535。varchar类型只保存添加的字符，用一个字节来记录长度，开始符一个字节，结束符一个字节，不像char使用填充符。
varchar存储变长数据，但存储效率没有char高。从存储空间考虑，用varcahr合适，从效率上考虑，用char合适。
二进制类型
| 类型 | 大小 | 用途 |
|---|---|---|
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
和字符串类型类似，只不过只能存储二进制数据。这几种BLOB类型最大的区别就是能够保存的最大长度不同。LONGBLOB的长度最大，TINYBLOB的长度最小。
复合类型
| 类型 | 大小 | 用途 |
|---|---|---|
| enum | 1字节 | 枚举，只能从enum()中取单一值 |
| set | 1字节 | 只能从set()取值 |
enum
枚举，只允许添加enum()中的数据，创建一个表，有id，姓名和性别字段。
mysql> create table test(id int(10),name char(5),sex enum('man','woman'));
查看结构
mysql> desc test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| sex | enum('man','woman') | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
向表中添加记录，若sex不是man或woman，数据库就会报错
mysql> insert into test values(1,'张三','m');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
按要求插入记录，并查看是否成功·
mysql> insert into test values(1,'张三','man');
mysql> insert into test values(1,'李莉','woman');
mysql> select * from test;
+------+--------+-------+
| id | name | sex |
+------+--------+-------+
| 1 | 张三 | man |
| 1 | 李莉 | woman |
+------+--------+-------+
set
只能从set()中取值，插入其他值就会报错。
创建一个表，有id，姓名，值字段
mysql> create table test(id int(5),name char(5),type set('开发','测试','运维'));
查看表结构，其中只有三个值选择
mysql> desc test;
+-------+---------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------------+------+-----+---------+-------+
| id | int(5) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| type | set('开发','测试','运维') | YES | | NULL | |
+-------+---------------------------------+------+-----+---------+-------+
插入其他数据就会报错
mysql> insert into test values(1,'张三','人事');
ERROR 1265 (01000): Data truncated for column 'type' at row 1
插入范围内的数据，查看记录
mysql> insert into test values(1,'张三','开发');
mysql> insert into test values(2,'李四','测试');
mysql> insert into test values(3,'王五','运维');
mysql> select *from test;
+------+--------+--------+
| id | name | type |
+------+--------+--------+
| 1 | 张三 | 开发 |
| 2 | 李四 | 测试 |
| 3 | 王五 | 运维 |
+------+--------+--------+
enum常用于性别字段，set常用于职位字段。
字段属性
| 属性 | 用途 |
|---|---|
| PRIMARY KEY | 主键 |
| DEFAULT | 默认值 |
| NULL | 数据列可包含NULL值 |
| NOT NULL | 数据列不允许包含NULL值 |
| AUTO_INCREMENT | 自动递增，用于数值类型 |
| COMMENT | 字段描述 |
| UNIQUE KEY | 唯一键 |
| CHARACTER SET name | 指定字符集 |
| ZEROFILL | 填充符改为0，int型长度为4，存入3的时候会添加0003 |
| UNSIGNED | 无符号，数值取值范围翻倍，规定插入数值不能为负数 |
primary key和unique key
primary key：叫做主键，也称为主键约束，是表中唯一标识每条记录的列，对表实施完整性约束。其作用就是使表中不含有重复的记录。
特点：
一个表中只能定义一个主键属性。
主键值必须唯一标识表中的记录，即主键数据表中唯一，不可能存在两行相同的主键值。
主键字段的数据不能为空（null）
unique key：叫做唯一键，用来保证字段中数据的唯一性，字段不能重复。
特点：
唯一键在一张表中可以存在多个。
唯一键允许字段类型为null，并且可以添加多个为null的记录。
创建一个表，有id，姓名，电话号码字段，其中id为主键，电话号码为唯一键。
mysql> create table test(id int(10) primary key,name char(5),phone int(10) unique key);
查看表结构
mysql> desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(10) | NO | PRI | NULL | |
| name | char(5) | YES | | NULL | |
| phone | int(10) | YES | UNI | NULL | |
+-------+---------+------+-----+---------+-------+
向表中添加记录，除主键外(主键不能为null)，其他字段都可添加null。
mysql> insert into test values(null,null,null);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into test values(1,null,null);
查看添加的记录
mysql> select * from test;
+----+------+-------+
| id | name | phone |
+----+------+-------+
| 1 | NULL | NULL |
+----+------+-------+
not null和null
not null为非空约束，在字段中使用not null，用户添加记录时如果没有添加数据，数据库就会报错。null表示该字段可以为空，用户添加记录时没有添加数据，就会以null填充，字段默认属性为null。
空值是不占用空间的，mysql中的null是占用空间的，就像是一个空容器一样。
创建一个表，有id，姓名，年龄字段，其中只有null为空值。
mysql> create table test1(id int(10),name char(5) not null,age tinyint(3) not null);
没有为id字段指定属性，默认属性为null，其他字段使用非空约束。
mysql> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(5) | NO | | NULL | |
| age | tinyint(3) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
在其中添加记录，在有非空约束属性的字段中不添加数据会产生报错，而null属性的字段并不受影响。
mysql> insert into test1 values(null,null,23);
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into test1 values(null,'张三',23);
这里不添加任何数据，并不是可以直接省略id字段，在数据库中的空值也是有值的，那就是null。查看记录是否插入成功。
mysql> select * from test1;
+------+--------+-----+
| id | name | age |
+------+--------+-----+
| NULL | 张三 | 23 |
+------+--------+-----+
default
default是默认值约束，用来指定某字段的默认值，如果用户没有为字段插入数据，则以默认值填充，默认值为null。
创建一个表，有id和姓名字段，其中姓名字段指定默认值
mysql> create table test2(id int(10),name char(5) default '机密');
查看表结构，Default就是字段的默认值，其中姓名字段默认值为机密
mysql> desc test2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(5) | YES | | 机密 | |
+-------+---------+------+-----+---------+-------+
只对id字段添加数据
mysql> insert into test2 (id)values(1);
查看插入的记录
mysql> select * from test2;
+------+--------+
| id | name |
+------+--------+
| 1 | 机密 |
+------+--------+
comment
comment用来为字段和表添加注释，在字段或表属性的最后添加。
例如：create table （***）comment ‘测试’；
这就是为表添加字段
创建一个表，有id和姓名字段，并为id字段和表添加注释。
mysql> create table test3 (id int(3) primary key comment '员工数量',name char(10))comment '员工信息';
对于描述的查看，并不会在表中或结构中显示，只能通过查询SQL语句中查看
mysql> show create table test3\G
*************************** 1. row ***************************
Table: test3
Create Table: CREATE TABLE `test3` (
`id` int(3) NOT NULL COMMENT '员工数量',
`name` char(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工信息'
auto_increment
auto_increment为数值型字段的自动增长， 表明mysql应该自动为该字段生成一个唯一没有用过的数（如果一条记录id为1，那么删除1，新添加的会是2）。只适用于整数类型的字段，数值从1开始，每增加一条记录，系统默认加1，不需要手动插入。
auto_increment常用于记录的计数字段，员工人数，记录条数等按顺序加1的字段。
创建一个表，有id，姓名字段，id字段为主键，并且设置自动递增。
mysql> create table test4(id int(10) primary key auto_increment,name char(5));
查看表的结构，Extra下面就是自动递增
mysql> desc test4;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
只为name字段添加三条记录
mysql> insert into test4 (name)values('张三'),('李四'),('王五');
查询记录
mysql> select *from test4;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----+--------+
zerofill与unsigned
可以转到===>传送<===查看。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
