MySQL数据类型

MySQL 数据类型有几大类：数字类型，日期时间类型，字符串类型，空间数据类型，JSON数据类型

1 数字类型

数字类型 有几个小类：
1）整数类型（精确值） -TINYINT，SMALLINT，MEDIUMINT，INT（INTEGER），BIGINT
2）定点类型（精确值） - DECIMAL，NUMERIC
3）浮点类型（近似值） - FLOAT，DOUBLE
4）比特值类型 - BIT

1.1 整数类型

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

BOOL， BOOLEAN   #  TINYINT(1) 的别名，值为零被视为false。非零值被认为是true。但值 TRUE 和 FALSE 仅是 1和0 的别名

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

INT[(M)] [UNSIGNED] [ZEROFILL]

INTEGER[(M)] [UNSIGNED] [ZEROFILL]   # INTEGER 是 INT 的别名

BIGINT[(M)] [UNSIGNED] [ZEROFILL]   # 有符号数的范围是 -9223372036854775808到 9223372036854775807。无符号的范围是0到 18446744073709551615。

SERIAL  # 等价于 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

注意：
对于整数类型，M表示最大显示宽度，最大显示宽度为255，显示宽度与类型可包含的值范围无关。

对于所有数字类型，如果指定ZEROFILL数字列，MySQL会自动将该UNSIGNED 属性添加到列中。

类型	存储 (Bytes)	有符号数范围	无符号数范围
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT	4	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT	8	-2^63 ~ 2^63 - 1	0 ~ 2^64 - 1

1.2 定点类型

存储精确的数值数据。在保持精确精度很重要时使用这些类型，例如 货币数据

# M是总位数（精度），D是小数点后的位数（刻度）。小数点和负号不计入 M。如果 D为0，则值没有小数点或小数部分
# M 最大 65 ，默认为10； D 最大30，要小于M，默认为0 
# DECIMAL  DEC NUMERIC FIXED 都是同一种数据类型
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

DEC[(M[,D])] [UNSIGNED] [ZEROFILL]

NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]

FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

# salary  范围-999.99 ~ 999.99
salary DECIMAL（5,2）

1.3 浮点数（近似值）

# 单精度浮点数
# 允许的数：-3.402823466E+38 ~ -1.175494351E-38, 0, 以及1.175494351E-38 ~ 3.402823466E+38
# 4个字节存储
# M表示的是最大长度，D表示的显示的小数位数。小数点和负号不计入 M
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]



#p 表示 bit位精度，其值的大小决定实际使用 Float 还 Double 类型存储。
# p 是 0 ~ 24 是 FLoat ，但没有 M 和 D
# p 是 25 ~ 53 是 Double， 没有 M 和 D
FLOAT(p) [UNSIGNED] [ZEROFILL]

# 双精度浮点数
# 允许的值范围：  -1.7976931348623157E+308 ~ -2.2250738585072014E-308, 0, 以及2.2250738585072014E-308 ~ 1.7976931348623157E+308
# 8个字节存储
# 下列三种申明实际是相同的
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL],

# 如果 REAL_AS_FLOAT SQL 模式开启了, REAL 就等同于 FLOAT 而不是 DOUBLE
REAL[(M,D)] [UNSIGNED] [ZEROFILL]

1.4 BIT

BIT数据类型被用于存储二进制数， 范围从1到64位。

# M 表示能存储的比特位数
# 如果位数不够，左侧填充0
 BIT(M)

2 日期和时间类型

DATE，DATETIME和TIMESTAMP类型 ， TIME类型 ， Year 类型。
MySQL的时区默认和操作系统的时区是一致的

# 日期
# DATE以'YYYY-MM-DD'格式显示值 ，但允许使用字符串或数字赋值
# 支持的范围是 '1000-01-01'到 '9999-12-31'
DATE

# 日期时间组合
# fsp 指定小数秒精度，0~6，默认为0，表示没有小数秒值
# 显示格式：YYYY-MM-DD hh:mm:ss[.fraction]， 允许使用字符串或数字赋值
# 支持的范围是 '1000-01-01 00:00:00.000000'到 '9999-12-31 23:59:59.999999'
DATETIME[(fsp)]

# 时间戳
# 存储自'1970-01-01 00:00:00'UTC 以来的秒数
# 范围是'1970-01-01 00:00:01.000000'UTC 到 '2038-01-19 03:14:07.999999'UTC
TIMESTAMP[(fsp)]

# 时间
# 显示格式： 'hh:mm:ss[.fraction]，
# 范围是'-838:59:59.000000' 到'838:59:59.000000'。time 不仅仅用于时间，还可以用于两个事件的时间差，小时数可能大于24
#允许使用字符串或数字赋值。 '11:12' -> '11:12:00',  '1112' / 1112  -> '00:11:12'
TIME[(fsp)]

# 年份
# 可以用字符串和数字赋值
# 两位数格式，以yy格式显示。'0'/0 ~ '99'/99：'0' to '69' 表示  2000 to 2069， '70' to '99' 表示 1970 to 1999.
# 四位数格式，以YYYY格式显示。 1901 to 2155、'1901' to '2155'
YEAR[(2)]
YEAR[(4)]

特别的，TIMESTAMP 时间戳类型存储时会将时区转换为UTC时区，获取值时，再转换为当前时区的值。如果改变MySQL 的时区，获取的值会与存储的时间戳值不同。其他时间类型存储不会转换时区。

TIMESTAMP 和 DATETIME 可以定义为 自动初始化和自动更新
1）设置为自动初始化即是设置默认值，当插入行中该字段的值为空， TIMESTAMP 和 DATETIME 的字段则使用默认值。SQL： DEFAULT CURRENT_TIMESTAMP
2）自动更新：在其他任何字段更新时， TIMESTAMP 和 DATETIME 的字段则更新。如果其他字段在更新前后没有变化（即用相同的值执行update语句）， TIMESTAMP 和 DATETIME 不更新。 SQL：ON UPDATE CURRENT_TIMESTAMP

# 默认值和更新时间都是 当前时间值
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

3 字符串类型

CHAR和VARCHAR类型， BINARY和VARBINARY类型， BLOB和TEXT类型， ENUM类型，SET类型。

1. char, varchar, text 是字符类型，长度按字符算。binary , varbinary, blob 是字节类型，长度按字节算。
   2）定义CHAR, VARCHAR, TEXT , ENUM, SET 字段时候可以指定字符集，字符校验规则。mysql默认使用 utf8mb4 字符集，校验规则使用utf8mb4_0900_ai_ci，中文一般要设置为 utf8mb4

# 固定长度字符串，长度不够时，存储时右边添加空格，取出时会去掉空格
# M表示长度，0~255，默认是1
[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]


# 变长字符串，M表示最大字符长度， M在 0 ~ 65535 之间。
# 实际的有效长度是取决于，这一行总大小和采用的字符集。
# 每一行所有字段共用 65535 个字节。utf8是采用 3 个字节存储一个字符的，所以最大能存 21844 个字符
# MySQL存储时，会加上 1/2 个字节的数字前缀，表示该数据的字节长度。如果字符串的字节数小于255，是一个字节，反之是两个字节。
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

# 小文本
# 最大255个字符，如果是多字节字符，实际有效最大字符数会更小
# 
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

# M 表示 字符个数，最大能存 65535 字符，如果是多字节字符，实际有效最大字符数会更小
# 存储时，会加上 2 个字节的数字前缀，表示该数据的字节长度
# 
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

# 最大  4,294,967,295 or 4GB (232 − 1) 字符，如果是多字节字符，实际有效最大字符数会更小
# 存储时，会加上 2 个字节的数字前缀，表示该数据的字节长度
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

# 固定长度二进制字符串
#  M表示字节长度，0~255，默认是1
BINARY[(M)]


# 变长二进制字符串
# M 表示最大字节长度
VARBINARY(M)

# 小字节文本
# 最大 255 个字节
# 存储时，会加上 1个字节的数字前缀，表示该数据的字节长度
TINYBLOB


# 字节文本
# 最大 65535 个字节
# 存储时，会加上 2个字节的数字前缀，表示该数据的字节长度
BLOB[(M)]

# 最大 16,777,215 (224 − 1）个字节
# 存储时，会加上 3 个字节的数字前缀，表示该数据的字节长度
MEDIUMBLOB

# 最大 4,294,967,295 or 4GB (232 − 1) 字节
# 存储时，会加上 4 个字节的数字前缀，表示该数据的字节长度
LONGBLOB


# 枚举类型
# 只能是一个值，且这个值是值列表中的一个
# 在内部以整数表示
# 值列表最大可以有 65,535 个不同的值
# 每个值要满足 M <= 255 and (M x w) <= 1020，M是字符个数，w 是当前字符集中最大的字符需要的字节数
ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

# 列表
# 可以有0个或多个元素，每个元素都必须是在列表中的，最大 64 个元素
# 每个元素要满足  M <= 255 and (M x w) <= 1020 ，M是字符个数，w 是当前字符集中最大的字符需要的字节数
SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

如果在 CHAR ，VARCHAR， TEXT 字段后面 加 ‘CHARACTER SET binary’， 那数据类型会变为 相应的二进制 类型

CREATE TABLE t
(
  c1 VARCHAR(10) CHARACTER SET binary,
  c2 TEXT CHARACTER SET binary,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

## 等同于

CREATE TABLE t
(
  c1 VARBINARY(10),
  c2 BLOB,
  c3 ENUM('a','b','c') CHARACTER SET binary
);

mysql 字符集详解： https://www.cnblogs.com/skying555/p/10512329.html
https://dev.mysql.com/doc/refman/8.0/en/charset.html
https://dev.mysql.com/doc/refman/8.0/en/data-types.html