MySQL-DataTypes 数据类型

MySQL 数据类型

Chapter 11 Data Types
https://dev.mysql.com/doc/refman/5.7/en/data-types.html

Mysql字符集和字符序

再见乱码：5分钟读懂MySQL字符集设置
https://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html

在数据的存储上，MySQL提供了不同的字符集支持。而在数据的对比操作上，则提供了不同的字符序支持，字符序也就是校验规则。
字符集 (character set) 定义了字符以及字符的编码。
字符序/校对 (collation) 定义了字符的比较规则。

MySQL支持多种字符集与字符序。
1、一个字符集对应至少一种字符序（一般是1对多）。
2、两个不同的字符集不能有相同的字符序。
3、每个字符集都有默认的字符序。例如，utf8 默认的字符序为 utf8_general_ci, utf8mb4 默认的字符序为 utf8mb4_general_ci，都是大小写不敏感的。
4、字符序命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以 _ci（大小写不敏感）、_cs（大小写敏感）或_bin（二进制）结束。
*_bin: binary case sensitive collation，也就是说是区分大小写的
*_cs: case sensitive collation，区分大小写
*_ci: case insensitive collation，不区分大小写

mysql大小写敏感

默认情况下 mysql 字符类型 varchar/char/text 是不区分大小写的，无论编码格式是 utf8 还是 utf8mb4 默认都不区分大小写，也就是默认都是大小写不敏感的。
因为默认情况下， utf8 的字符序是 utf8_general_ci, utf8mb4 的字符序是 utf8mb4_general_ci，最后的 ci 表示 case insensitive 即大小写不敏感。

方法一，select 查询时加 binary，不改动表结构

select * from table where binary str = 'aaa';
select * from table where BINARY str = 'aAA';

方法二，建表时加以标识
1、指定字段为 binary

create table some_table(
  str char(20) binary
);

2、指定某个字段的字符序为大小写敏感的

CREATE TABLE `T` (
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB;

3、指定整个表的字符序

CREATE TABLE `T2` (
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

方法三，对于已建好的表，修改表字段属性为大小写敏感

ALTER TABLE table_name MODIFY COLUMN code varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not null comment '大小写敏感的code';

utf8和utf8mb4

MySQL 在 5.5.3 之后增加了这个 utf8mb4 的编码，mb4就是 most bytes 4 的意思，专门用来兼容四字节的unicode。好在 utf8mb4 是 utf8 的超集，除了将编码改为 utf8mb4 外不需要做其他转换。当然，为了节省空间，一般情况下使用 utf8 也就够了。

那上面说了既然 utf8 能够存下大部分中文汉字,那为什么还要使用 utf8mb4 呢?
原来 mysql 支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff, 也就是 Unicode 中的基本多文种平面（BMP）。也就是说，任何不在基本多文本平面的 Unicode 字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和很多不常用的汉字，以及任何新增的 Unicode 字符等等。

查看mysql支持的字符集和字符序

show character set 查看可用字符集
13.7.5.4 SHOW CHARACTER SET Syntax
https://dev.mysql.com/doc/refman/5.6/en/show-character-set.html

语法：
SHOW CHARACTER SET [LIKE 'pattern' | WHERE expr]

例如：

MariaDB > show character set like '%utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf8    | UTF-8 Unicode    | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci |      4 |
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
+---------+------------------+--------------------+--------+

或者从 information_schema.CHARACTER_SETS 表中查

mysql> use information_schema;
mysql> select * from CHARACTER_SETS;

show collation 查看可用字符序
字符序的命名，以其对应的字符集作为前缀，如下所示。比如字符序 utf8_general_ci，标明它是字符集utf8的字符序。

13.7.5.5 SHOW COLLATION Statement
https://dev.mysql.com/doc/refman/5.6/en/show-collation.html
语法
SHOW COLLATION [LIKE 'pattern' | WHERE expr]

MariaDB > show COLLATION LIKE '%utf8mb4%';
+------------------------------+---------+-----+---------+----------+---------+
| Collation                    | Charset | Id  | Default | Compiled | Sortlen |
+------------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci           | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin                  | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci           | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci         | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci           | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci          | utf8mb4 | 227 |         | Yes      |       8 |
...

或者从 information_schema.COLLATIONS 表中查。

mysql> USE information_schema;
mysql> SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8";

variables character_set_ 当前字符集

SHOW VARIABLES LIKE 'character_set_%';
没设置的情况下，默认字符集如下：

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

character_set_client：客户端请求数据的字符集
character_set_connection：客户机/服务器连接的字符集
character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。
character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem，默认binary是不做任何转换的
character_set_results：结果集，返回给客户端的字符集
character_set_server：数据库服务器的默认字符集
character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。

variables collation_ 当前字符序

show variables like '%collation%';
没设置时默认字符序为

MariaDB > show variables like '%collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.016 sec)

MySQL中的字符集转换过程

MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：

使用每个数据字段的CHARACTER SET设定值；
若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
若上述值不存在，则使用character_set_server设定值。

将操作结果从内部操作字符集转换为character_set_results。

创建表时指定字符集

CREATE TABLE `article` (
`id`           BIGINT(20)      NOT NULL AUTO_INCREMENT  COMMENT 'id，自增主键',
`pathname`     VARCHAR(1024)   CHARACTER SET utf8    NOT NULL DEFAULT '' COMMENT '页面pathname',
`title`        VARCHAR(2048)   CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '文章title或页面title',
`create_time`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
-- mysql最大索引 768 个字节, utf8 占 3 个字节，768/3=256
UNIQUE (`pathname`(255))
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4;

指定了表的默认字符集为 utf8mb4，同时也指定了 pathname 字段使用 utf8 字符集，不冲突
其中 CHARSET 是 CHARACTER SET 的同义词

char/varchar/text

1、char(n) 和 varchar(n) 中括号中 n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入 n 个中文，但是实际会占用 n*3 个字节。
2、同时 char 和 varchar 最大的区别就在于 char 不管实际 value 都会占用 n 个字符的空间，而 varchar 只会占用实际字符应该占用的空间+1，并且实际空间+1<=n。
3、超过 char 和 varchar 的 n 设置后，字符串会被截断。
4、char 的上限为 255 字节，varchar 的上限 65535 字节（实际存储量还取决于整个数据表的最大行大小），text 的上限为65535。
5、char 在存储的时候会截断尾部的空格，varchar 和 text 不会。
6、varchar 会使用 1-3 个字节来存储长度，text 不会。

总体来说：
1、char，存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限 255。
2、varchar，存变长，速度慢，不存在空间浪费，不处理尾部空格，理论最大长度 65535，实际会受到表最大行大小限制（大约65535字节），这也包括了表中的所有列和索引。
3、text，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限 65535，会用额外空间存放数据长度，顾可以全部使用 65535。

varchar长度(为什么varchar长度经常定义为255?)

当 varchar(n) 后面的 n 非常大的时候我们是使用 varchar 好，还是 text 好呢？
从官方文档中我们可以得知当 varchar 大于某些数值的时候，其会自动转换为 text，大概规则如下：
大于 varchar(255) 变为 tinytext
大于 varchar(500) 变为 text
大于 varchar(20000) 变为 mediumtext
所以对于过大的内容使用 varchar 和 text 没有太多区别。

在 mysql 中，VARCHAR(3) 和 VARCHAR(255) 在存储方式上是没有区别的，都是1个字节表示字符串长度和字符串经utf-8编码后的字节。mysql5.0.3以前的版本varchar的最大长度就是255，之后是65535。而VARCHAR(256)之后表示长度的字节数会变成2个。其实在今天来说多一个字节也没什么区别，但为了兼容性，通常的数据库设计中还是会出现很多VARCHAR(255)。

TinyText/Text/MediumText/LongText

TinyText 最大长度 255 (2^8−1) bytes
Text 最大长度 65535 (2^16−1) bytes = 64 KiB
MediumText 最大长度 16,777,215 (2^24−1) bytes = 16 MiB
LongText 最大长度 4,294,967,295 (2^32−1) bytes = 4 GiB

TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB

BLOB保存二进制数据，TEXT保存字符数据

TEXT 不支持默认值

mysql text 类型没有默认值，如果该字段没有值，则该字段是空，即is null

根据 mysql5.0以上版本 strict mode (STRICT_TRANS_TABLES) 的限制：
不支持对not null字段插入null值
不支持对自增长字段插入’’值，可插入null值
不支持 text 字段有默认值

除 TEXT/BLOB 外单行长度不能超过65535

8.4.7 Limits on Table Column Count and Row Size
https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

建表 sql

CREATE TABLE row_sile_test (
    id bigint auto_increment primary key,
    col1 varchar(10000) DEFAULT NULL,
    col2 varchar(10000) DEFAULT NULL,
    col3 varchar(10000) DEFAULT NULL
) ENGINE = INNODB DEFAULT CHARSET = utf8;

报错：
[42000][1118] Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

原因：
MySQL 要求除 text 和 blob 外单行长度不超过 65535。
未开启 strict mode 时，varchar 长度大于 65535 时（指的是字节数大于65535，即字段长度大于等于 21846），MySQL 会自动转换为 TEXT

解决：
varchar(10000) 改为 text
注意：MySQL 5.7 默认开启 strict mode，开启 strict mode 时，varchar(21846) 会直接报错：[42000][1074] Column length too big for column ‘col1’ (max = 21845); use BLOB or TEXT instead

CREATE TABLE row_sile_test
(
    id bigint auto_increment primary key,
    col1 text DEFAULT NULL,
    col2 text DEFAULT NULL,
    col3 text DEFAULT NULL
) ENGINE = INNODB
  DEFAULT CHARSET = utf8;

bit

MySQL 提供了允许存储位值的 BIT 类型。
BIT(m) 可以存储多达m位的值，m的范围在1到64之间。如果省略，默认值为1。

要指定位值,可以使用 b'value' 符。value是一个用0和1编写的二进制值。例如, b’111’ 和 b’100000000’ 分别表示7和128。

bin(id) 以二进制展示 bit 值。

boolean/tinyint

MySQL 没有内置的布尔类型。但是它使用 TINYINT(1)。为了更方便，MySQL提供 BOOLEAN 或 BOOL 作为 TINYINT(1) 的同义词。
在 MySQL 中，0 被认为是 false，非零值被认为是 true。
要使用布尔文本，可以使用常量 TRUE 和 FALSE 来分别计算为 1 和 0。

即使创建表时字段类型定义为 BOOLEAN 之后用 show create table 看表结构时也会发现字段类型变成 TINYINT(1) 了

tinyint/int/bigint

tinyint/int/bigint等数据长度

Type	Storage (Bytes)	有符号最小值	有符号最大值	无符号最大值
TINYINT	1	-128	127	255
SMALLINT	2	-32768	32767	65535
MEDIUMINT	3	-8388608	8388607	16777215
INT	4	-2147483648	2147483647	4294967295
BIGINT	8	$-2^{63}$	$2^{63}-1$	$2^{64}-1$

11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

int(5)括号内的长度是显示长度

int(M), M 指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关

“整型”的长度实际上可以理解为”显示长度”, 如果该字段开启 “Zerofill/补零”就能很明显地知道它的作用.

这个M=5我们可以简单的理解成为, 我们建立这个长度是为了告诉MYSQL数据库我们这个字段的存储的数据的宽度为5位数, 当然如果你不是5位数(只要在该类型的存储范围之内)MYSQL也能正常存储

MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。
显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。
也就是说，int后面括号内的长度并不影响数据的存储精度，长度只和显示有关

“高性能MySQL” 书中在”4.1 选择优化的数据类型”中提到:
MySQL 可以为整数类型指定宽度, 例如 INT(11), 对大多数应用这是没有意义的: 它不会限制值的合法范围, 只是规定了 MySQL 的一些交互工具(例如 MySQL 命令行客户端)用来显示字符的个数. 对于存储和计算来说, INT(1) 和 INT(20) 是相同的

MySQL 5.7 手册 “12.2.5 Numeric Type Attributes”:
MySQL 支持用括号包含的数字限定整型的显示长度. 比如 INT(4) 限定了整型的显示长度为 4 个字符, 对于小于 4 个字符的数字, 有些数据库软件会用”空格”来补齐小于 4 个位数的数字.

这个显示长度并不会限制该字段的数字存储范围, 同时, 也不会阻止大于指定显示长度的数字写入该字段. 比如, SMALLINT(3) 的字段和 SMALLINT 的数字存储范围都是 -32768 to 32767, 如果存储的数字超过 3 个位数仍然是允许被存入 SMALLINT(3) 字段, 而且以其本来的位数显示.

如果配合 ZEROFILL 属性, 将用 0 来补齐. 比如 INT(4) ZEROFILL 字段, 数字 5 会被存储为 0005.

MySQL 整型长度的含义
https://www.jianshu.com/p/61293b416335

MySQL unique和null

MySQL 唯一约束 unique 要求该列的值唯一，但如果该列是可为 null 的，允许有多个 null 值。

Does MySQL ignore null values on unique constraints?
https://stackoverflow.com/questions/3712222/does-mysql-ignore-null-values-on-unique-constraints

default null与default ‘’

《高性能mysql》中是这么说的：

尽量避免NULL

通常情况下最好指定列为 NOT NULL，除非真的需要存储 NULL 值；mysql表定义时如果没有指定列为NOT NULL，默认都是允许NULL的；

如果查询中包含可为NULL的列，对mysql来说更难优化。因为可为NULL的列，使得索引、索引统计、值比较，都更复杂；

可为NULL的列会使用更多的存储空间，在MYSQL里也需要特殊处理。

当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引；

通常，把可为NULL的列改为NOT NULL带来的性能提升比较小，所以调优时没有必要首先修改这种情况，除非确定这会导致问题；

但是如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。当然也有例外，比如InnoDB使用单独的bit存储NULL的值，对稀疏数据有很好的空间效率。这一点不适用于MyISAM。
（稀疏数据：是指很多值都是NULL，少数值是非NULL）

1：空值(‘’)是不占用空间的
2: MySQL中的NULL其实是占用空间的。官方文档说明:

NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.

所以 mysql设计表时建议不要用default NULL
string类型的可以default ‘’ int类型的可以default 0

数据库表某字段设置default为Null好，还是“”好，或者是Empty String好呢？手机端我只想返回空
https://segmentfault.com/q/1010000006758650/a-1020000006759600

值得收藏：一份非常完整的MySQL规范
https://mp.weixin.qq.com/s/QAzb6yCS9NqOY066ZBcuSg

为什么建议MySQL列属性尽量用 NOT NULL

1、NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回非空行的结果集。
比如 t.name 列可以是 null
id, name
1 张三
2 null
3 李四
则 select * from t where name !='李四' 只会返回 id=1 的数据，也就是 name 列不是 null 的

2、使用 concat 函数拼接时，首先要对各个字段进行非 NULL 判断，否则只要任何一个字段为空都会造成拼接的结果为 NULL

3、当用count函数进行统计时，NULL 列不会计入统计

5、NULL 列需要更多的存储空间，一般需要一个额外的字节作为判断是否为 NULL 的标志位。
同一类型，同样长度的key，如果其中一个是可为null的，则会比 not null 列的key长度多1，因为
key_len 的长度一般跟这三个因素有关，分别是数据类型，字符编码，是否为 NULL。

面试官问，为什么建议MySQL列属性尽量用 NOT NULL ？
https://mp.weixin.qq.com/s/PIKUol_7AR1CU4FehJAJLw

MySQL 日期和时间

DATETIME(无时区,9999年)

MySQL DATETIME 存储包含日期和时间的值，MySQL以 YYYY-MM-DD HH:MM:SS 格式显示 DATETIME 值。
另外，DATETIME 值可以包括格式为 YYYY-MM-DD HH:MM:SS[.fraction] 例如：2017-12-20 10:01:00.999999 的尾数有小数秒。当包含小数秒精度时，DATETIME 值需要更多存储。

默认情况下，DATETIME 的值范围为 1000-01-01 00:00:00 至 9999-12-31 23:59:59
DATETIME 值使用 5 个字节进行存储。

DATETIME 存储毫秒(精度)

MySQL 建表的时候时间戳很多会选用 datetime，默认长度0，但是这个时间精度是到秒的，这个时候 datetime 会把时间戳毫秒精度进行四舍五入。
例如传入的时间戳为 1552319999999，格式化后为”2019-03-11 23:59:59 999” 插入数据库后会变成”2019-03-12 00:00:00”

如果需要精度到毫秒，需要设置 datetime 的长度为3，即 DATETIME(3) 是保留3位毫秒数，修改后解决该问题。

info_create_time     datetime(3)    not null comment '创建时间',
info_update_time     datetime(3)    not null comment '更新时间',

TIMESTAMP(有时区,2038年)

1、TIMESTAMP 占 4 个字节，而 DATETIME 需要 8 个字节。
见 11.7 Data Type Storage Requirements
https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

2、TIMESTAMP 值范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。如果要存储超过 2038 的时间值，则应使用 DATETIME 而不是 TIMESTAMP。
DATETIME 的值范围为 1000-01-01 00:00:00 至 9999-12-31 23:59:59

3、MySQL 将 TIMESTAMP 存储在 UTC(有时区)值中，也就是说 TIMESTAMP 是带时区的，它会自动检索当前时区并进行转换。但是，MySQL 存储 DATETIME 值是没有时区的。
这意味着如果使用 TIMESTAMP 数据来存储日期和时间值，则在将数据库移动到位于不同时区的服务器时时间的值可能不一样，所以应该认真考虑这个问题。

4、从 mysql5.7 开始 datetime 字段也可以指定默认值，并且格式和 timestamp 一样

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

11.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

TIMESTAMP 的默认值和自动更新

timestamp 字段有三个属性：

是否允许NULL。默认为not null。
默认值。
可以设定为 default CURRENT_TIMESTAMP 或 default 某个常量。若定义时缺省，对于第一个出现的timestamp字段，默认为CURRENT_TIMESTAMP，对于其他 timestamp 字段，默认为 ‘0000-00-00 00:00:00’。 Default 可以指定为 null，前提是本字段允许null。比如声明 field2 timestamp null default null。
On update CURRENT_TIMESTAMP
是否在更新其他字段时自动将该 timestamp 字段修改为当前时间戳。对于第一个出现的timestamp字段，默认为On update CURRENT_TIMESTAMP，对于其他 timestamp 字段，没有此行为。

1、如果定义时 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句都有，列值为默认使用当前的时间戳，并且自动更新。
2、如果不使用 DEFAULT 或 ON UPDATE 子句，那么它等同于 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 也就是不带任何修饰的首个 timestamp 类型就是 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 的

3、如果只有 DEFAULT CURRENT_TIMESTAMP 子句，而没有 ON UPDATE 子句，列值默认为当前时间戳但不自动更新。
4、如果没用 DEFAULT 子句，但有 ON UPDATE CURRENT_TIMESTAMP 子句，列默认为0并自动更新。
5、如果有一个常量值 DEFAULT，该列会有一个默认值，而且不会自动初始化为当前时间戳。如果该列还有一个 ON UPDATE CURRENT_TIMESTAMP 子句，这个时间戳会自动更新，否则该列有一个默认的常量但不会自动更新。
换句话说，你可以使用当前的时间戳去初始化值和自动更新，或者是其中之一，也可以都不是。（比如，你在定义的时候可以指定自动更新，但并不初始化。）下面的字段定义说明了这些情况：

自动初始化和更新:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

只自动初始化:
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

只自动更新
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

只是给一个常量（注：0000-00-00 00:00:00）
ts TIMESTAMP DEFAULT 0

MySQL的timestamp类型自动更新问题
http://www.yayu.org/look.php?id=144

11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

enum 枚举类型

枚举类型对应的数字从1开始

ENUM的字段值不区分大小写

创建带有enum类型字段的表

CREATE TABLE `enum_tests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('pending','success','closed') COLLATE utf8mb4_unicode_ci NOT NULL default 'success',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

修改enum字段的枚举值

ALTER TABLE inviter_relationship_transaction
CHANGE mode mode ENUM('auto', 'manual', 'task') default 'auto' null;

关于 MySQL enum 类型的一些测试
https://laravel-china.org/articles/6927/some-tests-on-the-mysql-enum-type

DECIMAL(10,0) 总共10位小数0位

MySQL 中使用浮点数和定点数来表示小数：

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）
定点类型只有一种，就是 DECIMAL

DECIMAL 从 MySQL 5.1 引入，列的声明语法是 DECIMAL(P,D)
P 精度表示有效数字数的精度，P 的范围为1〜65，默认值是 10。
D 标度表示小数点后的位数，D 的范围是0~30，但不得超过P，若不指定默认是0。
MySQL 要求 D 小于或等于(<=)P。
DECIMAL(P，D) 表示列可以存储 D 位小数的 P 位数。十进制列的实际范围取决于精度和刻度。

column_name DECIMAL(P); 相当于 column_name DECIMAL(P,0); 不包含小数部分或小数点
column_name DECIMAL; 如果 P 也忽略，相当于 column_name DECIMAL(10); P 的默认值为 10

decimal 的默认值是 0

示例
amount DECIMAL(6,2); amount 列最多可以存储 6 位数字，小数位数为 2 位; 因此，amount 列的范围是从 -9999.99 到 9999.99。

使用场景
对于精度比较高的东西，比如money，建议使用 decimal 类型，不要使用 float,double, 因为他们容易产生误差。

MysqlDataTruncation 超过小数位数报错

alert_id decimal(10, 4) null
存入 1186145 报错
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value for column ‘alert_id’ at row 1

float/double/decimal选择

float 和 double 的数据精确度取决于分配给每种数据类型的存储长度，其中float分配了4字节，而double分配了8字节。

1、如果你要表示的浮点型数据转成二进制之后能被32位float存储，或者可以容忍截断，则使用float，这个范围大概为要精确保存6位数字左右的浮点型数据
比如10分制的店铺积分可以用float存储，小商品零售价格(1000块之内)

2、如果你要表示的浮点型数据转成二进制之后能被64位double存储，或者可以容忍截断，这个范围大致要精确到保存13位数字左右的浮点型数据
比如汽车价格,几千万的工程造价

3 相比double，已经满足我们大部分浮点型数据的存储精度要求，如果还要精益求精，则使用decimal定点型存储
比如一些科学数据，精度要求很高的金钱

float(P,D) unsigned 总共P位，小数后D位, unsigned 代表不允许使用负数
double(P,D) unsigned 总共P位，小数后D位, unsigned 代表不允许使用负数

当前位置 : 首页 » 文章分类 : 开发 » MySQL-DataTypes 数据类型