目录
- mysql数据类型
- Text
- Number
- Date
- Text
- char
- varchar
- char与varchar
- text
- mediumtext
- longtext
- Enum
- Set
- 二进制类型
- JSON
- 结论
- Number
- bit
- tinyint
- smallint
- mediumint
- int/integer
- bigint
- 说明
- decimal
- float
- double
- Date/Time
- date
- time
- year
- datetime
- timestamp
- datetime与timestamp的区别
mysql数据类型
在 MySQL 中，有三种主要的类型：
- Text (文本)
- Number (数字)
- Date/Time (日期/时间) 类型
Text
数据类型 | 描述 |
CHAR(size) | 保存固定长度的字符串（可包含字母、数字以及特殊字符） 在括号中指定字符串的长度 最多 255 个字符 |
VARCHAR(size) | 保存可变长度的字符串（可包含字母、数字以及特殊字符） 在括号中指定字符串的最大长度 最多 255 个字符 注意：如果值的长度大于 255，会被转换为 TEXT 类型 |
TINYTEXT | 存放最大长度为 255 个字符的字符串 |
TEXT | 存放最大长度为 65,535 个字符的字符串 |
BLOB | 用于 BLOBs（Binary Large OBjects） 存放最多 65,535 字节的数据。 |
MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串 |
MEDIUMBLOB | 用于 BLOBs（Binary Large OBjects） 存放最多 16,777,215 字节的数据 |
LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串 |
LONGBLOB | 用于 BLOBs (Binary Large OBjects) 存放最多 4,294,967,295 字节的数据。 |
ENUM(x,y,z,...) | 允许输入可能值的列表 可以在 ENUM 列表中列出最大 65535 个值 如果列表中不存在插入的值，则插入空值 注意：存储的值是按照输入的顺序排序的 可以按照此格式输入可能的值 |
SET | 与 ENUM 类似，但 SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择 |
Number
数据类型 | 描述 |
TINYINT(size) | 带符号 -128 到 127 ，无符号 0 到 255 |
SMALLINT(size) | 带符号范围 -32768 到 32767 无符号 0 到 65535 size 默认为 6 |
MEDIUMINT(size) | 带符号范围 -8388608 到 8388607 无符号的范围是 0 到 16777215 size 默认为 9 |
INT(size) | 带符号范围 -2147483648 到 2147483647 无符号的范围是 0 到 4294967295 size 默认为 11 |
BIGINT(size) | 带符号的范围是 -9223372036854775808 到 9223372036854775807 无符号的范围是0到 18446744073709551615 size 默认为 20 |
FLOAT(size,d) | 带有浮动小数点的小数字 在 size 参数中规定显示最大位数 在 d 参数中规定小数最大位数 |
DOUBLE(size,d) | 带有浮动小数点的大数字 在 size 参数中规显示定最大位数 在 d 参数中规定小数的最大位数 |
DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型，允许固定的小数点 在 size 参数中规定显示最大位数 在 d 参数中规定小数点右侧的最大位数 |
Date
数据类型 | 描述 |
DATE() | 日期。格式：YYYY-MM-DD 支持的范围是从 '1000-01-01' 到 '9999-12-31' |
DATETIME() | *日期和时间的组合 格式：YYYY-MM-DD HH:MM:SS 支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' |
TIMESTAMP() | *时间戳 TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储 格式：YYYY-MM-DD HH:MM:SS 支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC |
TIME() | 时间 格式：HH:MM:SS 支持的范围是从 '-838:59:59' 到 '838:59:59' |
YEAR() | 2 位或 4 位格式的年 4 位格式所允许的值：1901 到 2155 2 位格式所允许的值：70 到 69 表示从 1970 到 2069 |
Text
char
char[(m)]: m表示字符数；最多255 (一个中文是一个字符) (默认1)
定长，简单粗暴，浪费空间，存取速度快
存储：
存储char类型的值时，会往右填充空格来满足长度
例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储
检索：
在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）varchar
varchar[(m)]: m表示字符数；(v8中, 必须指定字符数)
变长，精准，节省空间，存取速度慢，有几位占几位
字符长度范围：0-65535（如果大于21845会提示用其他类型 。
mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
存储：
varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来
强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
检索：
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容char与varchar
对于空间问题，假设char和varchar的字符数都是4，那么当要存储的字符数<4时，
varchar确实会节省空间；但是当要存储的字符数=4时，varchar反而会浪费空间。
- char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），
然后修改sql_mode让其现出原形
set sql_mode = ‘PAD_CHAR_TO_FULL_LENGTH’;
set sql_mode = ‘’; 还原 - 虽然 CHAR 和 VARCHAR 的存储方式不太相同,但是对于两个字符串的比较,都只比 较其值,忽略 CHAR 值存在的右填充,
即使将 SQL MODE 设置为 PAD_CHAR_TO_FULL LENGTH 也一样,但这不适用于like
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。
因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
SQL优化建议：
- 将定长的数据放在前面，不定长的数据放在后面
- 最好不要在一张表中混用char和varchar
255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。
text
text: 2^16 - 1个字符 不设置长度, 就算设置了长度, 也不起作用, (用于当不知道属性的最大长度时)
mediumtext
mediumtext: 2^24 - 1个字符
longtext
longtext: 2^32 - 1个字符
Enum
枚举类型：如单选框，多选框
单选 只能在给定的范围内选一个值
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
# 插入记录的时候，对应字段的值只能从enum()已规定好的值中选取
# ENUM 最多只能包含 65,535 个不同的枚举值
# 用于选项固定，不经常修改
# ===========================================
1. 创建表结构时指定的枚举值都会分配一个内部索引，索引的下标从 1 开始
2. 注意：下标并不是从 0 开始，而 0 则具有其它的意义
空字符串错误值的索引为 0，这样，我们可以直接使用 0 值来查询那些插入的或更新的无效的枚举值
(新版中, 插入不存在, 直接报错)
3. NULL 值的索引为 NULL
枚举值 索引
NULL NULL
'' 空字符串 0
'Mercury' 1
'Venus' 2
'Earth' 3
4. ENUM 最多只能包含 65,535 个不同的枚举值
5. 如果在数字上下文中检索 ENUM 值，则返回列值的索引
SELECT enum_col+0 FROM tbl_name;
6. 当在 ENUM 列上使用 SUM() 或 AVG() 等聚合函数时，
因为这些函数的参数必须是一个数字，所以 MySQL 会自动使用它们的索引值作为参数。
也就是说，对于需要计算的场景，都会使用内部索引。
其实，真实的枚举值，只有在插入或者显示或者查询时才会用到。
7. ENUM的字面量处理
在创建表结构时，MySQL 会自动删除 ENUM 枚举值的尾随空格，例如会把 'medium ' 转换成 'medium'。
检索时，MySQL 会自动将存储的内部索引转换为定义时指定的相应的 enum 枚举值字面量。
8. 不建议使用看起来像数字的枚举值来定义 ENUM 列，
因为这很容易让人感到困惑，分不清传递(引用) 的到底是枚举值字面量还是内部索引。
例如，以下列的枚举成员的字符串值为 '0'、'1' 和 '2'，而数字索引值为 1 、2 和 3
numbers ENUM('0','1','2')
如果我们在插入数据或者更新数据时指定存储 2 ，因为会被解释为索引值，所以实际存储的枚举值为 '1' ( 索引为 2 的值 ）。
而如果我们存储 '2' ，因为枚举值字面量 '2' 存在，所以存储的值也为 2 。
但如果我们存储 '3' ，因为枚举值字面量 '3' 并不存在，那么它就会被视为是内部索引 3 ，进而存储的实际值其实是 '2'
mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1 |
| 2 |
| 2 |
+---------+
如果要确定 ENUM 列的所有可能值，
SHOW COLUMNS FROM tbl_name LIKE 'enum_col' 语句可以解析出 enum_col 列中的所有 enum 定义
9. 如果一个 ENUM 列添加了 NULL 约束，那么这个 ENUM 列就允许 NULL 值，且默认的值就是 NULL
如果一个 ENUM 列添加了 NOT NULL 约束，那么它的默认值就是第一个枚举值。
10. ENUM 枚举值的排序问题
因为 ENUM 类型存储的是枚举值的内部索引，
所以 ENUM 值根据其索引号进行排序，具体显示出来，则取决于定义列是的枚举成员顺序。
https://www.twle.cn/c/yufei/mysqlfav/mysqlfav-basic-enum2.html
例如，如果在定义列时，指定了 'b' 在 'a' 前面 ('b'，'a')，那么 'b' 的顺序就会在 'a' 之前，且空字符串在非空字符串之前排序，NULL 值在所有其他枚举值之前排序
也就是排序的顺序默认是 NULL '' 'b' 'a'
这是一个大坑啊，为了避免这个坑，为了在 ENUM 列上使用 ORDER BY 子句时防止出现意外结果，则需要做如下选择
指定 ENUM 列的排序顺序使用字母顺序表
或者使用 ORDER BY CAST (col AS CHAR) 或 ORDER BY CONCAT(col) 确保 enum 列按词法排序而不是索引编号排序
11. ENUM 数据类型的一些限制
1. 枚举值不能是表达式，即使该表达式用于计算字符串值。
例如，下面的建表语句是无效的，会执行失败，因为 CONCAT()函数不能用于构造枚举值
CREATE TABLE sizes (
size ENUM('small', CONCAT('med','ium'), 'large')
);
2. 不能使用用户变量作为枚举值。例如下面的语句也是无效的
SET @mysize = 'medium';
CREATE TABLE sizes (
size ENUM('small', @mysize, 'large')
);
3. 建议不要使用数字用作枚举值，
因为它不会通过适当的 TINYINT 或 SMALLINT 类型保存在存储上。
而且，如果你错误地引用 ENUM 值，很容易混淆枚举字面量和底层索引值 ( 可能不相同 )
4. ENUM 列定义中的重复值会导致警告，如果启用了严格的 SQL 模式，则会出错
# ===========================================Set
集合类型：
多选 在给定的范围内可以选择一个或一个以上的值
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
# 插入记录的时候，对应字段的值只能从set()规定好的值中任意组合进行插入
# 去重
# 最多 64 个二进制类型
TinyBlob、Blob、MediumBlob、LongBlobJSON
# =======================================================================================================
MySQL支持JSON数据类型。相比于Json格式的字符串类型，JSON数据类型的优势有：
存储在JSON列中的JSON文档的会被自动验证。无效的文档会产生错误；
最佳存储格式。存储在JSON列中的JSON文档会被转换为允许快速读取文档元素的内部格式。
存储在JSON列中的任何JSON文档的大小都受系统变量max_allowed_packet的值的限制，
可以使用JSON_STORAGE_SIZE()函数获得存储JSON文档所需的空间。
JSON值的局部更新
在MySQL8.0中，优化器可以执行JSON列的局部就地更新，而不用删除旧文档再将整个新文档写入该列。局部更新的条件：
1. 正在更新的列被声明为JSON；
2. 该UPDATE语句使用任一的三个函数 JSON_SET()， JSON_REPLACE()或 JSON_REMOVE()更新列；
3. 输入列和目标列必须是同一列；
4. 所有更改都使用新值替换现有数组或对象值，并且不向父对象或数组添加任何新元素；
5. 新值不能大于旧值；
创建JSON值:
JSON数字
'10'
JSON字符串
'"HELLO"'
JSON数组
["abc", 10, null, true, false]
JSON对象
{"k1": "value", "k2": 10}
嵌套
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}
例子:
mysql> CREATE TABLE t_json (jdoc JSON) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.73 sec)
mysql> INSERT INTO t_json VALUES('[1,2]');
Query OK, 1 row affected (0.17 sec
mysql> INSERT INTO t_json VALUES('{"key1":"value1","key2":"value2"}');
Query OK, 1 row affected (0.27 sec)
mysql> INSERT INTO t_json VALUES('"HELLO"');
Query OK, 1 row affected (0.20 sec)
JSON_TYPE()函数:
JSON_TYPE()函数尝试将传入的值其解析为JSON值。如果值有效，则返回值的JSON类型，否则产生错误：
例如:
SELECT JSON_TYPE('["a","b",true,13]'); -- ARRAY
JSON_ARRAY()函数:
JSON_ARRAY()接收传入的值列表（可以为空），返回包含这些值的JSON数组;
例如:
SELECT JSON_ARRAY("ab",false,13); -- ["ab", false, 13]
SELECT JSON_ARRAY(); -- JSON_ARRAY()
JSON_OBJECT()函数:
JSON_OBJECT() 接收传入的键值对列表（可以为空），并返回包含这些键值对的JSON对象：
例如:
select json_object("key1", "a", "key2", 20); -- {"key1": "a", "key2": 20}
select json_object(); -- {}
JSON_MERGE_PRESERVE()函数:
JSON_MERGE_PRESERVE() 获取两个或多个JSON文档并返回组合结果：
例如:
SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}'); -- ["a", 1, {"key": "value"}]
因此我们也可以使用以上三种方法向表中添加JSON值，可以一定程度地避免输入格式错误：
mysql> INSERT INTO t_json VALUES(JSON_ARRAY('json_array'));
Query OK, 1 row affected (0.19 sec)
mysql> INSERT INTO t_json VALUES(JSON_OBJECT('key','hello'));
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO t_json VALUES(JSON_MERGE_PRESERVE(JSON_OBJECT('key','hello'),JSON_ARRAY(1,2)));
Query OK, 1 row affected (0.14 sec)
JSON值的规范化，合并和自动包装:
解析字符串并发现字符串是有效的JSON文档时，它在被解析时也会被规范化。
对于重复的键（key）,后面的值（value）会覆盖前面的值。
例如:
SELECT JSON_OBJECT('x',1,'y',2,'x','a','x','b'); -- {"x": "b", "y": 2}
合并JSON值:
MySQL8.0.3及更高版本中，有两种合并函数：JSON_MERGE_PRESERVE()和 JSON_MERGE_PATCH()。
下面具讨论它们的区别。
合并数组：
SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]');
-- [true, false]
SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]');
-- [1, 2, "a", "b", "c", 1, 2, true, false]
合并数组时，
JSON_MERGE_PATCH只保留最后传入的数组参数，
而JSON_MERGE_PRESERVE则按传入顺序将数组参数连接。
两者必须有2个参数及以上
合并对象:
SELECT JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}');
-- {"a": 4, "b": 2, "c": 5, "d": 3}
SELECT JSON_MERGE_PRESERVE('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}');
-- {"a": [3, 4], "b": 2, "c": [3, 5], "d": 3}
合并对象时:
合并对象时，对于重复键，JSON_MERGE_PRESERVE只保留最后传入的键值，
而JSON_MERGE_PRESERVE重复键的所有值保留为数组。
搜索和修改JSON值:
在了解搜索和修改JSON值之前，先来看看JSON的路径语法。
路径语法:
1. .keyName：JSON对象中键名为keyName的值；
2. 对于不合法的键名（如有空格），在路径引用中必须用双引号"将键名括起来，例，."key name"；
3. [index]：JSON数组中索引为index的值，JSON数组的索引同样从0开始；
4. [index1 to index2]：JSON数组中从index1到index2的值的集合；
5. .*: JSON对象中的所有value；放入一个数组中
6. [*]: JSON数组中的所有值；
7. prefix**suffix: 以prefix开头并以suffix结尾的路径；
8. **.keyName为多个路径，如对于JSON对象'{"a": {"b": 1}, "c": {"b": 2}}','$**.b'指路径$.a.b和$.c.b；
9. 不存在的路径返回结果为NULL；
10. 前导$字符表示当前正在使用的JSON文档
例子：对于数组[3, {"a": [5, 6], "b": 10}, [99, 100]]
$[1]为{"a": [5, 6], "b": 10}。
[1].a为[5, 6]。
$[1].a[1]为 6。
$[1].b为 10。
$[2][0]为 99。
搜索:
JSON_EXTRACT提取JSON值
JSON对象
SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name');
-- "Taylor"
SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*');
-- [29, "Taylor"]
JSON数组
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');
-- "b"
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]');
-- ["b", "c"]
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[*]');
-- ["a", "b", "c"]
修改:
1. JSON_REPLACE 替换值（只替换已经存在的旧值）
2. JSON_SET 设置值（替换旧值，并插入不存在的新值）
3. JSON_INSERT 插入值（插入新值，但不替换已经存在的旧值）
4. JSON_REMOVE 删除JSON数据，删除指定值后的JSON文档
JSON_REPLACE与JSON_SET的区别：
// 旧值存在
mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere');
+----------------------------------------------------------------+
| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
+----------------------------------------------------------------+
| {"id": 29, "name": "Mere"} |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', "Mere");
+------------------------------------------------------------+
| JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
+------------------------------------------------------------+
| {"id": 29, "name": "Mere"} |
+------------------------------------------------------------+
1 row in set (0.00 sec)
// 旧值不存在
mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
+---------------------------------------------------------------+
| JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
+---------------------------------------------------------------+
| {"id": 29, "name": "Taylor"} |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
+-----------------------------------------------------------+
| JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
+-----------------------------------------------------------+
| {"id": 29, "cat": "Mere", "name": "Taylor"} |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
JSON_INSERT和JSON_SET:
// 旧值存在
mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[1]', 4);
+-------------------------------------+
| JSON_INSERT('[1, 2, 3]', '$[1]', 4) |
+-------------------------------------+
| [1, 2, 3] |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET('[1, 2, 3]', '$[1]', 4);
+----------------------------------+
| JSON_SET('[1, 2, 3]', '$[1]', 4) |
+----------------------------------+
| [1, 4, 3] |
+----------------------------------+
1 row in set (0.00 sec)
//旧值不存在
mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[4]', 4);
+-------------------------------------+
| JSON_INSERT('[1, 2, 3]', '$[4]', 4) |
+-------------------------------------+
| [1, 2, 3, 4] |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET('[1, 2, 3]', '$[4]', 4);
+----------------------------------+
| JSON_SET('[1, 2, 3]', '$[4]', 4) |
+----------------------------------+
| [1, 2, 3, 4] |
+----------------------------------+
1 row in set (0.00 sec)
JSON_REMOVE:
mysql> SELECT JSON_REMOVE('[1, 2, 3]', '$[1]');
+----------------------------------+
| JSON_REMOVE('[1, 2, 3]', '$[1]') |
+----------------------------------+
| [1, 3] |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_REMOVE('[1, 2, 3]', '$[4]');
+----------------------------------+
| JSON_REMOVE('[1, 2, 3]', '$[4]') |
+----------------------------------+
| [1, 2, 3] |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_REMOVE('{"id": 29, "name": "Taylor"}', '$.name');
+-------------------------------------------------------+
| JSON_REMOVE('{"id": 29, "name": "Taylor"}', '$.name') |
+-------------------------------------------------------+
| {"id": 29} |
+-------------------------------------------------------+
1 row in set (0.00 sec)
# =========================================================================================================结论
结论:
1、经常变化的字段用varchar；
2、知道固定长度的用char；
3、超过255字节的只能用varchar或者text；
4、能用varchar的地方不用text；
5、能够用数字类型的字段尽量选择数字类型而不用字符串类型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了；
6、同一张表出现多个大字段，能合并时尽量合并，不能合并时考虑分表，原因请考 优化InnoDB表BLOB,TEXT列的存储效率https://www.jb51.net/article/157892.htmNumber
bit
bit [(m)]: m为二进制位数，默认为1，最多64tinyint
tinyint [(m)] [unsigned] [zerofill]:
# m用作显示时候的宽度；
# unsigned表示数字为无符号类型，默认为signed类型
# zerofill表示不够宽度的时候用0来填充，默认用空格；不能用于负数
# 由于MySQL没有布尔类型所以要用：tinyint(1) 来构造。(现在可以用bit来代替)
# 如果为数值列指定了 ZEROFILL ，MySQL 会自动添加 UNSIGNED 属性到列中。
# 另外, unsigned 以及 zerofill 都不是标准属性
# signed: -2^7 ~ 2^7 - 1
# unsigned: 0 ~ 2^8 - 1
+------+
| x |
+------+
| -128 | #-129存成了-128
| -128 | #有符号，最小值为-128
| 127 | #有符号，最大值127
| 127 | #128存成了127
+------+smallint
2个字节
mediumint
3个字节
int/integer
signed: -2^31 ~ 2^31 - 1
unsigned: 0 ~ 2^32 - 1
bigint
signed: -2^63 ~ 2^63 - 1
unsigned: 0 ~ 2^64 - 1
说明
在表达式或 UNION 查询中涉及列时，会自动忽略 ZEROFILL 属性。
使用 ZEROFILL 属性时要注意，如果将大于显示宽度的值存储在具有 ZEROFILL 属性的整数列中，
为某些复杂连接生成临时表时可能会遇到问题。
因为在这些情况下，MySQL 会假定数据值符合列显示宽度
PS：注意：为该整类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，超过宽度了，原样输出
其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了
默认的显示宽度，都是在最大值的基础上加1
有符号和无符号的最大数字需要的显示宽度均为10，
而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的
最后：整形类型，其实没有必要指定显示宽度，使用默认的就okdecimal
decimal [(m,[d])] [unsigned] [zerofill]:
m表示小数点前后的数字的总数，负号不算，m最大65
d表示小数点后的数字的位数，如果省略d，只有m的话，就是四舍五入取整；
如果有d，但位数不足的话，补0，反正要d位，d最大30
存储数字是准确的，因为内部采用字符串的形式float
float [(m,d)] [unsigned] [zerofill]:
单精度浮点数（非准确小数值），数值越大越不准，4个字节
m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30double
double [(m,d)] [unsigned] [zerofill]:
双精度浮点数（非准确小数值），精度比float要高，数值越大也会变得不准确，8个字节
m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30Date/Time
date
date: YYYY-MM-DD (1000-01-01 / 9999-12-31)
YY-MM-DD
变形，就是指中划线 ( - ) 可以替换为任意字符，除了数字 0-9:
例如:
YYYY/MM/DD
YYYY^MM^DD
同时还支持 YYYYMMDD 和 YYMMDD 的字符串形式，例如 '20180913' 和 '180913'。
还支持 YYYYMMMDD 和 YYMMDD 的数字格式，例如 20180913 和 180913。
插入两位年份时，<=69，以20开头，比如50, 结果2050
>=70，以19开头，比如71，结果1971
插入年份时，尽量使用4位值time
time: HH:MM:SS ('-838:59:59'/'838:59:59')
单独插入时间时，需要以字符串的形式，按照对应的格式(只能是:分隔)插入year
year: YYYY (1901 - 2155) 指定类型是要么是year要么是year(4), 不管那种, 最后都是year(4)
4位 or 2位的 字符 or int 年份都可以datetime
datetime: YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59)timestamp
timestamp: YYYY-MM-DD HH:MM:SS (1970-01-01 00:00:00 / 2038)
insert into t13 values();
insert into t13 values(null);对于 datetime 和 timestamp 类型，支持 YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS，以及它们的变形
这个变形其实和 DATE 一样，而且就是指中划线 ( - ) 可以替换为任意字符，除了数字 0-9 。
更进一步，这两个类型可以针对日期部分和时间部分使用不同的分隔符
例如:
YYYYY^MM^DD HH+MM+SS
YY@MM@DD HH^MM^SS
datetime 和 timestamp 两种时间类型还可以精确到 微秒 ，
具体的形式就是支持在 秒 后面使用 6 位精度的小数来支持，
例如:
2018-09-13 21:15:10.111111
例如:
2018-09-13 21:15:10.11
日期和时间部分与小数秒部分之间的唯一可用的分隔符号是小数点 ( . )
日期与时间部分的分隔符不一定就要使用空格 ( ' ' )，还可以使用字符 T ，
例如:
2018-09-13 21:15:10 和 2018-09-13T21:15:10 是等价的
同时还可以使用没有任何分隔符的形式，
比如 YYYYMMDDHHMMSS 或 YYMMDDHHMMSS，
例如 '20180913211510' 与 2018-09-13 21:15:10 是等价的
但 071122129015 则是非法的，并不是因为年份缺少了，而是因为分钟太大 ( 90 )，然后会被视为 0000-00-00 00:00:00
因为 MySQL 同时支持超大整数类型，
所以，数字形式的 YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 也是被支持的，
例如 20180913211510 和 180913211510 被认为与 2018-09-13 21:15:10 是等价的
============注意啦，注意啦，注意啦===========
1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入
2. 插入年份时，尽量使用4位值
3. 插入两位年份时，<=69，以20开头，比如50, 结果2050
>=70，以19开头，比如71，结果1971datetime与timestamp的区别
######################################################################################
datetime与timestamp的区别
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别。
1. DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
2. timestamp 会将值的时区从当前时区转换为 UTC 然后存储，并在需要显示和检索时从 UTC 转换回当前时区
但对于 datetime 类型，什么都不会发生，值是什么时区，存储的就是什么时区
默认情况下，timestamp 和 datetime 都会将当前连接的 MySQL 服务器的时区当作当前时区，当然了，这个时区是可配置的，而且可以针对每个连接单独配置。
从某些方面说，在数据转移或者在不同地方显示时，只要设置了一样的时区，那么数据就是一致的，否额
datetime 的值虽然存储和显示的时候都是同一个值，但可能不是我们想要的，因为时区错了
timestamp 虽然可以保证时间是正常的，但存储的值和显示的值可能会不一样，可能会导致我们错觉发生
3. DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
4. MySQL 还为 datetime 和 timestamp 两种日期时间类型提供了自动赋值功能。
也就是在 MySQL INSERT 时可以自动初始化为当前日期时间，在 MySQL Update 时自动更新为当前时间。
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
====================不要怀疑, 就是update, 没有insert=============================================
两个约束
DEFAULT CURRENT_TIMESTAMP 该列会自动设置当前时间为默认值
ON UPDATE CURRENT_TIMESTAMP 自动更新为当前时间戳
1. DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 指示在 insert 操作时自动插入当前日期时间
2. DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 指示在 update 操作时自动更新为当前日期时间
对于timestamp, insert的时候可以这样values() / values(null) / 自已指定 / values(default); 在update的时候, 自动更新
对于datetime, insert的时候可以这样values() / 自已指定 / values(default) / 不能values(null); 在update的时候, 自动更新
=====================================================================================================
2.使用了 default 约束，当没有使用 ON UPDATE CURRENT_TIMESTAMP 约束时，
该列会自动使用给定的默认值，但不会自动更新为当前时间戳
而默认的值取决于 default 子句是指定 CURRENT_TIMESTAMP 还是常量值。
如果使用 CURRENT_TIMESTAMP，默认值是当前时间戳
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP
);
=====================================================================================================
5. 对于这三种日期时间类型，无效的值将会转换为相应的 零 值，
也就是，date 类型会转换为 0000-00-00 ，
datetime 类型会转换为 0000-00-00 00:00:00 ，
而 timestamp 则会转换为 1970-01-01 00:00:00
6. 日期时间类型，还可以各取所长
例子： create table employee(d date, t time, dt datetime);
insert employee values(now(), now(), now());
#####################################################################################################
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
