MySQL 数据类型

数据类型分类

分类	数据类型	说明
数值类型	bit(M)	位类型,M为指定位数,默认为1,范围1-64
数值类型	tinyint [unsigned]	带符号范围-128 ~ 127,无符号的0~255.默认有符号
数值类型	smallint [unsigned]	带符号是 -2^15 次方 ~ 2^15 -1 ,无符号是2^16 - 1
数值类型	bool	使用0 / 1 表示真假
数值类型	int [unsigned]	带符号的是-2^31 次方 ~ 2^31 - 1,无符号是2^32 - 1
数值类型	bigint [unsigned]	带符号的是-2^63 次方 ~ 2^63 - 1,无符号是2^64 - 1
数值类型	float( M,D) [unsigned]	M指定显示长度,D指定小数位数,占用4字节
数值类型	double(M, D) [unsigned]	表示比float精度更大 的小数,占用空间8字节
数值类型	dectmal(M, D) [unsigned]	定点数M指定长度,D表示小数点的位数
文本 . 二进制类型	char(size)	固定长度字符串,最大255
文本 . 二进制类型	varcahr(size)	可变长度字符串,对大长度65535
文本 . 二进制类型	blob	二进制数据
文本 . 二进制类型	text	大文本,不支持全文索引,不支持默认值
时间日期	date / datetime / timestamp	日期类型(yyyy-mmm-dd) (yyyy - mm - dd hh:mm:ss ) timestamp时间戳
string类型	enum类型	enum是一个字符串对象,其值来自表创建时在列规定中显示枚举的一列值
string类型	set类型	set是一个字符串对象,可以有0/多个值, 其值来自表创建时规定的允许的一列值. 指定包括多个set成员的set列值时各成员之间用逗号隔开,这样set成员值本身不能包含逗号

bit类型

• bit类型在显示时,按照ascll码对应的的值显示

 create table bit;
insert into bit values(65,65);
MariaDB [test1]> select * from bit;
+------+------+
| id   | a    |
drop+------+------+
|   65 | A    |
+------+------+
1 row in set (0.01 sec)

当所存值只有0/1时,可如下定义,可以节省空间

MariaDB [test1]> create table bi(gender bit(1));
Query OK, 0 rows affected (0.01 sec)

MariaDB [test1]> insert into bi values(0);
Query OK, 1 row affected (0.00 sec)

MariaDB [test1]> insert into bi values(1);
Query OK, 1 row affected (0.00 sec)

MariaDB [test1]> insert into bi values(2);
ERROR 1406 (22001): Data too long for column 'gender' at row 1

decimal

decimal(m,d)[unsigend] : 定点数m指定m指定长度,d表示小数点的位数

• decimal(5, 2)表示的范围是-999.99~99.99
• decimal(5,2) unsigned表示的范围0~999.99
• decimal和float表示的精度不一样
• decimal的精度更加准确

MariaDB [test1]> insert into bit values(100,32.123453,32.123456);
Query OK, 1 row affected (0.01 sec)

MariaDB [test1]> select * from bit;
+------+-------------+-------------+
| id   | salary      | salary2     |
+------+-------------+-------------+
|  100 | 32.12345505 | 32.12345600 |
+------+-------------+-------------+
1 row in set (0.00 sec)

char

char(L) : 固定长度字符串, L是可以存储的长度,单位为字符,最大长度值可以为255
char(2)表示可以存放两个字符,可以是字母或者汉字,但是不能超过2个,最多只能是255

varchar

varchar(len) : 可变长度字符串,L表示字符长度,最大长度65535个字节

• len指定为0~65535之间的值,但是又1-3个字节用于记录数据大小,即有效字节为35532
• 此处len在使用不停编码表时长度不同,在使用UTF8时一个字符占用3个字节,len的最大长度为65532 / 3 = 21844 ; 在使用gbk编码, len最大长度为65532 / 2 = 32766(在gbk编码中,一个字符占用2个字节)

char 和varchar比较

• 如果数据定长使用char
• 如果数据边长使用varchar
• 定长的磁盘空间比较浪费,但是效率高
• 变长的磁盘空间比较节省但是效率低

日期和时间类型
常见类型:

• datetime 时间日期类型 'yyyy-mm HH:II:ss’表示范围从1000~9999,占用8字节
• date: 日期’yyyy-mm-dd’,占用3字节
• timestamp : 时间戳 ,从1970年开始的yyyy-mm-dd HH:ii:ss格式和datatime完全一致,占用4字节

MariaDB [test1]> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.00 sec)

//插入数据
MariaDB [test1]> insert into birthday (t1,t2) values('2000-7-1','2010-7-1 12:00:00');
Query OK, 1 row affected (0.00 sec)

MariaDB [test1]> select * from birthday ;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2000-07-01 | 2010-07-01 12:00:00 | 2019-09-04 17:26:49 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

//更新数据
MariaDB [test1]> update birthday set t1 = '2020-9-4';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test1]> select * from birthday ;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2020-09-04 | 2010-07-01 12:00:00 | 2019-09-04 17:41:21 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

enum和set

• enum: 枚举,'单选’类型.
• enum(‘选项1’,‘选项2’,‘选项3’…)
  该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；当添加枚举值时，也可以添加对应的数字编号。
• set : 集合,'所选’类型;
• set(‘选项值1’,‘选项值2’,‘选项值3’, …);
  该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，… 最多64个。

create table people(
    -> username varchar(30),
    -> hobby set('听','说','读')
    -> ,gender enum('男','女'));
ariaDB [test1]> insert into people values('Juse','听,说',2);
Query OK, 1 row affected (0.01 sec)

MariaDB [test1]> select * from people;
+----------+---------+--------+
| username | hobby   | gender |
+----------+---------+--------+
| 张三     | 听,说   | 男     |
| Juse     | 听,说   | 女     |
+----------+---------+--------+
2 rows in set (0.01 sec)

MariaDB [test1]> select * from people where gender=2;
+----------+---------+--------+
| username | hobby   | gender |
+----------+---------+--------+
| Juse     | 听,说   | 女     |
+----------+---------+--------+
1 row in set (0.00 sec)

集合中查询使用find_in_set函数

select * from people where find_in_set('听', hobby);