MySQL基础(十四)——数据类型

本篇文章主要是对MySQL学习时的一些总结，作为学习笔记记录。

数据类型

mysql中的数据与C/C++中的数据类型，都有其内部定义的数据类型，数据类型表明了该类型下数据的属性和性质。

分类

数值型	整型
	小数	定点数
		浮点数
字符型	较短的文本	char，varchar
	较长的文本	text，blob(较长的二进制数据)
日期型

对于mysql中常见的数据类型大致可以按照上表进行分类，但除此之外也存在其它数据类型，如位类型，enum类型，set类型等。

数值类型

整型

整数类型	字节	范围
Tinyint	1	有符号：-128~127
		无符号：0~255
Smallint	2	有符号：-32768~32767
		无符号：0~65535
Mediumint	3	有符号：同理
		无符号：同理
Int，integer	4	有符号：同理
		无符号：同理
Bigint	8	有符号：同理
		无符号：同理

对于整型来说，有以下几点需要注意：

• 如果没有显式说明无符号还是有符号，则默认是无符号，如果想要设置有符号，则需要添加signed关键字
• 如果插入的数值超出了整型的范围，会产生out of range异常，并且插入数值会转变为接近的临界值
• 如果不设置长度，则会有默认的长度(此处的长度代表了显示的最大宽度，如果不够会用0在左边填充，但此时必须搭配zerofill使用)

DROP TABLE IF EXISTS int_info ;


CREATE TABLE int_info (
  t1 TINYINT (7) ZEROFILL,
  t2 TINYINT (7) SIGNED
) ;


INSERT INTO int_info VALUES(0,-128);

INSERT INTO int_info VALUES(255,127);

INSERT INTO int_info VALUES(-1,-129);

INSERT INTO int_info VALUES(256,128);

小数

对于浮点数来说：

浮点数类型	字节	范围
float(M,D)	4	很大
double(M,D)	8	很大

对于定点数来说：

定点数类型	字节	范围
DEC(M,D)	M+2	最大取值范围与double相同，给定decimal的有效取值范围由M和D决定
DECIMAL(M,D)

需要注意：

• M表示整数部分位数+小数部分位数，D表示小数部分位数，如果超过范围，则插入临界值
• M和D都可以省略
  • 如果是decimal，则M默认为10，D默认为0
  • 如果是float和double，则会根据插入的数值的精度来决定精度
• 定点型的精确度更高，如果要求插入数值的精度较高(货币运算等)时，可以考虑使用
• 在建表时，为了存储方便和节约内存，所选择的类型在满足使用的条件下越简单越好，数据类型字节长度越小越好

DROP TABLE IF EXISTS float_info ;


CREATE TABLE float_info (f1 FLOAT, f2 DOUBLE, f3 DECIMAL) ;


INSERT INTO float_info VALUES(123.4523,123.4523,123.4523);
                           
INSERT INTO float_info VALUES(123.456,123.456,123.456);
                           
INSERT INTO float_info VALUES(123.4,123.4,123.4);

INSERT INTO float_info VALUES(1523.4,1523.4,1523.4);

字符型

对于字符型来说，有多种方式可以选择：

• 对于较短的文本，可以使用char，varchar
• binary和varbinary用于保存较短的二进制
• enum用于保存枚举
• set用于保存集合
• 对于较长的文本，可以使用text，blob(较大的二进制)

常用的字符类型一般为char和varchar，主要差别为：

字符类型	写法	M含义	特点	存储消耗	效率
char	char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较节省	低

对于enum和set类型来说，其使用方法为：

# enum类型用法
DROP TABLE IF EXISTS enum_info;


CREATE TABLE enum_info (e1 ENUM ('a', 'b', 'c'));


INSERT INTO enum_info VALUES('a');

INSERT INTO enum_info VALUES('b');

INSERT INTO enum_info VALUES('c');

INSERT INTO enum_info VALUES('d');

# set类型用法
DROP TABLE IF EXISTS set_info;


CREATE TABLE set_info (s1 SET ('a', 'b', 'c'));


INSERT INTO set_info VALUES('a');

INSERT INTO set_info VALUES('a,b');

INSERT INTO set_info VALUES('A,B,C');

INSERT INTO set_info VALUES('A,B,C,D');

日期型

对于日期型的数据来说：

• date保存日期
• time保存时间
• year保存年份
• datetime保存日期和时间
• timestamp保存日期和时间

日期和时间类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 00:00:00
timestamp	4	19700101080001	2038年的某个时刻
time	3	-838:59:59	838:59:59
year	1	1901	2155

而对于同样可以表示日期和时间的datetime和timestamp来说，也存在着区别：

日期类型	字节	范围	时区影响
datetime	8	1000-01-01 00:00:00~9999-12-31 00:00:00	不受
timestamp	4	19700101080001~2038年的某个时刻	受

DROP TABLE IF EXISTS time_info;


CREATE TABLE time_info (t1 DATETIME, t2 TIMESTAMP);


INSERT INTO time_info VALUES(NOW(),NOW());

SELECT * FROM time_info;

SHOW VARIABLES LIKE 'time_zone';

SET time_zone = '+9:00';