MySQL中的数据类型 函数

大表格整理

数据类型

数据类型类别	数据类型	描述	存储需求
数值类型	TINYINT	1字节，范围为 -128 到 127，或 0 到 255（无符号）	1 字节
	SMALLINT	2字节，范围为 -32,768 到 32,767，或 0 到 65,535（无符号）	2 字节
	MEDIUMINT	3字节，范围为 -8,388,608 到 8,388,607，或 0 到 16,777,215（无符号）	3 字节
	INT 或 INTEGER	4字节，范围为 -2,147,483,648 到 2,147,483,647，或 0 到 4,294,967,295（无符号）	4 字节
	BIGINT	8字节，范围为 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（或无符号）	8 字节
	decimal(m, d) [unsigned] 或 NUMERIC:	定点数类型，M指定显示长度，d指定小数位数，可存储精确的小数。	可变
	float[(m, d)] [unsigned]	M指定显示长度，d指定小数位数，占用空间4个字节。精度为大约 7 位。MySQL在保存值时会进行四舍五入	4 字节
	DOUBLE	双精度浮点数，存储 8 字节，精度为大约 15 位	8 字节
日期和时间类型	数据类型	描述	存储需求
	DATE	日期，格式为 YYYY-MM-DD	3 字节
	TIME	时间，格式为 HH:MM:SS	3 字节
	DATETIME	日期和时间，格式为 YYYY-MM-DD HH:MM:SS	8 字节
	TIMESTAMP	时间戳，格式为 YYYY-MM-DD HH:MM:SS，范围为 1970-01-01 到 2038-01-19	4 字节
	YEAR	年份，格式为 YYYY，范围为 1901 到 2155	1 字节
字符串类型	数据类型	描述	存储需求
	CHAR(L)	定长字符串，长度L为 0 到 255 字符	定长，1 字节/字符
	VARCHAR(L)	变长字符串，长度L为 0 到 65,535 字符（取决于最大行大小）	变长，1 字节/字符 + 1/2 字节
	TEXT	长文本数据，最大长度为 65,535 字符	可变
	TINYTEXT	非常小的文本数据，最大长度为 255 字符	可变
	MEDIUMTEXT	中等长度的文本数据，最大长度为 16,777,215 字符	可变
	LONGTEXT	非常大的文本数据，最大长度为 4,294,967,295 字符	可变
	BLOB	二进制大对象，存储二进制数据，最大长度为 65,535 字节	可变
	TINYBLOB	非常小的二进制数据，最大长度为 255 字节	可变
	MEDIUMBLOB	中等长度的二进制数据，最大长度为 16,777,215 字节	可变
	LONGBLOB	非常大的二进制数据，最大长度为 4,294,967,295 字节	可变
	enum('选项1','选项2','选项3',...);	enum：枚举，“单选”类型，些选项的每个选项值依次对应数字:1,2,3,….最多65535（数字编号是自动生成的）	1-2 字节，取决于元素个数
	set('选项值1','选项值2','选项值3', ...);	集合，“多选”类型，set 类型的选项值在内部是通过位图表示的，每个选项值对应一个二进制位，因为每个选项值占用一个 bit，8 bytes = 64 bits，最多只能有 64 个选项值	1-8 字节，取决于集合元素个数
其他类型	数据类型	描述	存储需求
	bit[(M)]	位字段类型，存储二进制值，M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。并且bit字段在显示时，是按照ASCII码对应的值显示。	1-8 字节，取决于位数
	BOOL 或 BOOLEAN	布尔类型，本质上是 TINYINT(1)，值为 0 或 1	1 字节
	SERIAL	自动增量类型，本质上是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE	8 字节

其他使用

集合查询

• find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；str_list 用逗号分隔的字符串。

varchar(len),len到底是多大，这个len值，和表的编码密切相关：

• varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。

  • 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844，因为utf中，一个字符占用3个字节。

  • 如果编码是gbk，varchar(n)的参数n最大是65532/2=32766，因为gbk中，一个字符占用2字节）。

如何选择定长或变长字符串？

• 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5

  • 定长的磁盘空间比较浪费，但是效率高,因为它直接开辟好对应的空间。

• 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。

  • 变长的磁盘空间比较节省，但是效率低。
  • 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

日期函数

函数	说明
current_date()	获得当前日期（年月日）
current_time()	获得当前时间（时分秒）
current_timestamp()	获得当前时间戳
date_add('2017-10-28', interval 10 day)	在日期的基础上加上指定时间
date_sub('2017-10-1', interval 2 day)	在日期的基础上减去指定时间
datediff('2017-10-10', '2016-9-1')	计算两个日期之间相差的天数
now()	当前日期时间

字符串函数

函数	说明
charset(ename)	获取 表的 ename 列的字符集
concat(name, '的语文是',chinese,'分，数学是',math,'分')	格式化字符串输出（将不同科目成绩拼接成一句话）
length(name)	计算字符串的字节数（根据字符集编码确定）
replace(ename, 'S', '上海')	将字符串中所有的 S 替换成 '上海'
substring(ename, 2, 2)	截取字符串的第二个到第三个字符
concat(lcase(substring(ename, 1, 1)),substring(ename,2))	以首字母小写的方式显示字符串

数学函数

函数	说明
abs(-100.2)	返回绝对值
ceiling(23.04)	向上取整
floor(23.7)	向下取整
format(12.3456, 2)	保留2位小数（四舍五入）
rand()	生成随机数

函数	说明
user()	查询当前用户
md5('admin')	对字符串进行 md5 加密，生成32位摘要字符串
database()	显示当前正在使用的数据库
SELECT SHA2('mypassword', 256);	SHA2()函数用于生成SHA-2系列的加密哈希值。
ifnull(val1, val2)	如果 val1 为 null，返回 val2，否则返回 val1 的值