内容
1 数值类型
2 浮点数类型
3 字符串类型
4 日期类型
5 其他类型
数值类型
类型 大写 范围 范围(无符号)
TINYINT 1 byte (-128,127) (0,255)
SMALLINT 2 bytes (-32 768,32 767) (0,65 535)
MEDIUMINT 3 bytes (-8 388 608,8 388 607) (0,16 777 215)
INT 4 bytes (-2 147 483 648,2 147 483 647) (0,4 294 967 295)
BIGINT 8 bytes (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615)
注:int(1) 和int(10)有什么区别?int(m)
1 数值类型中后面的m的大小只有在字段设置了 zerofill 属性后,才有区别,如果字段没有设置了这个属性,那么写多少都没有区别;
2 当字段设置zerofill 后,m的含义是字段显示的宽度,当实际存储的值的宽度没有m大时,显示时会在前面补0,当宽度超过m后,没有区别;实际存储的值还是一样的,int也是用4个字节存储,换句话说,m不影响实际存储,只是在显示上有区别
看下面例子:
mysql> create table t3(id int(4) zerofill);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(11111);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 0001 |
| 0002 |
| 0003 |
| 11111 |
+------+
浮点数类型
Float: 4字节存储,如果没有指定精度和标度,按照原来的精度存储,有效数字是8位,超过8位数字会四舍五入
Double: 8个字节存储,如果没有指定精度和标度,按照原来的精度存储,有效数字是16位
Decimal: 定点数据类型
定义 这些类型的数据时,同时需要指定其长度和精度 ,即用Float(M,D)来表示,M表示该值的总共长度,D表示小数点后面的长度,M和D又称为精度和标度。
如果 float 和 double 不指定精度,则按照实际的精度来显示, decimal 不指定时,默认为(10,2)。
这里,还有一个问题,对于 float 和 double,如果不指定精度,则不会存储小数点末尾的0,只有指定了具体的精度值,才会存储小数点末尾的0。
字符串类型
类型 大小 含义
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据
1 关于char(n)和varchar(n):n都是指定了该列最大可存储的字符数,如 char(6) 说明该列只能存储6个字符
2 char是定长的,如char(4),当存储的值没有达到4个字符时,如存储‘ab’, 则实际存储时会在后面添加空字符串补齐,实际存储的值是‘ab ’,char字段存储值的末尾有空格时,会被丢弃,查询时会根据trim把多余空格去掉 ; varchar(4)是定长的,当实际存储值没有达到4个字符时,不会补齐,会按实际内容存储;
3 char限制了最大的字符数是255,不管是什么编码时,都是可允许最大存储字符是255个,实际存储占用的空间根据不同的编码方式不定;
4 varchar最大存储限制是65535个字节,根据不同的编码方式,可存储的最大字符数不一样,例如utf-8编码时,一个中文字符占3个字节,那么实际可存储的最大字符数: n= (65535-1-2)/3 个,减1时当字段设置default null时,会用一个字节来标识,当长度超过255时,会用2个字节来存储字段的实际长度,小于255是用1个字节存储长度;
5 当实际存储超过n时,如果字段模式严格模式,则报错,如果是非严格模式,则会把超过的部分截取丢掉;
6 mysql一行的数据有大小限制,一行数据存储空间最大为65535,所以一般情况,当一个表中不仅仅只有varchar一列时,该varchar列的最大存储量是达不到65535的,因为其他列也占用了存储;
7 text的最大存储也是65535byte,但是text列相当于在数据页只存储了地址,真实的数据是存储在外存的,所以不会像varchar一样占用每行64k这个最大存储量太多
8 text不能默认为空字符串,如果没有值,那么是null
日期类型
日期时间类型 占用空间 日期格式 最小值 最大值 零值表示
DATETIME 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59 0000-00-00 00:00:00
TIMESTAMP 4 bytes YYYY-MM-DD HH:MM:SS 1980-01-01 00:00:01 UTC 2040-01-19 03:14:07 UTC 00000000000000
DATE 4 bytes YYYY-MM-DD 1000-01-01 9999-12-31 0000-00-00
TIME 3 bytes HH:MM:SS -838:59:59 838:59:59 00:00:00
YEAR 1 bytes YYYY 1901 2155 0000
TIMESTAMP和DATETIME比较
相同点:两者都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期。
不同点:他们的的存储方式,大小(字节),表示的范围不同。
TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。
DATETIME,不做任何改变,基本上是原样输入和输出。
总结:TIMESTAMP和DATETIME 都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期, 除了存储方式和存储范围以及大小不一样,没有太大区别。但对于跨时区的业务,TIMESTAMP更为合适。
其他类型
ENUM
CREATE TABLE tickets (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
priority ENUM('Low', 'Medium', 'High') NOT NULL
);
SET
参考:[https://www.cnblogs.com/wtsgtc/p/10387007.html](https://www.cnblogs.com/wtsgtc/p/10387007.html)
多选字符串数据类型,适合存储“多个值”。
设定set的时候,同样需要设定“固定的几个值”;存储的时候,可以存储其中的若干个值。
设定set的格式:字段名称 SET("选项1","选项2",...,'选项n')
同样的,set的每个选项值也对应一个数字,依次是1,2,4,8,16...,最多有64个选项
使用的时候,可以使用set选项的字符串本身(多个选项用逗号分隔),也可以使用多个选项的数字之和(比如:1+2+4=7)
-1.创建及插入
/* 创建表格 */
create table set_table(
id int auto_increment primary key,
hobby set('music','movie','swimming','footbal')
);
/* 使用选项的字符串格式 */
insert into set_table(id,hobby) values(null,'M','music');
/* 使用选项的字符串格式 */
mysql> insert into enum_set_table(id,hobby) values(null,'F','music,movie,footbal');
/* 使用选项的数字格式 */
insert into set_table(id,hobby) values(null,1);
/* 使用选项的数字格式,15=1+2+4+8 <=> 'music,movie,swimming,footbal' */
insert into set_table(id,hobby) values(null,15);
/* 查询结果 */
select * from enum_set_table;
--2.查询
--找出SET_col包含value set成员的行。
SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
--在其它地方找出set_col包含value的行,甚至是在另一个SET成员的子字符串中。
SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
--寻找包含第1个set成员的值。
SELECT * FROM tbl_name WHERE set_col & 1;
--寻找一个确切匹配的值。
SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
--应注意将set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。