MySQL数据类型

MySQL 支持SQL数据类型的几个类别：数字类型、日期和时间类型、字符串（字符和字节）类型、空间类型和 JSON数据类型。

==========================================数据类型  ==========================================

MySQL支持所有标准的SQL数值数据类型。这些类型包括精确的数值数据类型（INTEGER、SMALLINT、DECIMAL和numeric），以及近似的数值数据格式（FLOAT、REAL和DOUBLE PRECISION）。关键字INT是INTEGER的同义词，关键字DEC和FIXED是DECIMAL的同义词。MySQL将DOUBLE视为DOUBLE PRECISION（一种非标准扩展）的同义词。MySQL还将REAL视为DOUBLE PRECISION（一种非标准变体）的同义词，除非启用了REAL_as_FLOAT SQL模式。数据类型BIT支持InnoDB、MyISAM、 MEMORY和NDB。

主要包括以下五大类：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

1、数字类型：
对于整数数据类型，M 表示最小显示宽度。最大显示宽度为 255。显示宽度与类型可以存储的值范围无关。
对于浮点和定点数据类型，M 是可以存储的总位数。
对于数字数据类型(int、FLOAT、DOUBLE 和 DECIMAL)，ZEROFILL 属性已弃用;如果为数字列指定 ZEROFILL，则 MySQL 会自动将 UNSIGNED 属性添加到该列。在未来版本的 MySQL 中删除对它的支持。请考虑使用其他方法来产生此属性的效果。例如，应用程序可以使用 LPAD（） 函数将数字填充到所需的宽度，或者它们可以将格式化的数字存储在 CHAR 列中。
SERIAL 是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。

bit（M）位值类型。M 表示每个值的位数，范围为 1 到 64。如果省略 M，则默认值为 1。
BOOL, BOOLEAN 是 TINYINT（1） 的同义词。值 0 被视为 false。非零值被视为 true；但是，值TRUE和FALSE分别只是1和0的别名。

    整数类型          字节          范围（signed）              范围（unsigned）          用途
    TINYINT          1字节        (-128，127)                      (0，255)            小整数值
    SMALLINT         2字节       (-32768，32767)               (0，65535)           大整数值
    MEDIUMINT        3字节       (-8388608，8388607)            (0，16777215)      大整数值
    INT或INTEGER     4字节    (-2147483648，2147483647)      (0，4294967295)      大整数值：21亿，10位数
    BIGINT           8字节   (-9223372036854775808,9223372036854775807)  (0,18446744073709551615) 极大整数值 19位数
    FLOAT            4字节   (-3.4E+38，1.17E-38)，  0，     (1.17E-38，3.4E+38) 0，(1.17E-38，3.4E+38) 单精度浮点数值
    DOUBLE           8字节 (1.79E+308，2.22E-308)，  0，     (2.22E-308，1.79E+308) 0，(2.22E-308，1.79E+308) 双精度浮点数值
    DECIMAL/dec(fix用于其他系统兼容)       对DECIMAL(M,D)   其中M表示十进制数字总的个数，D表示小数点后面数字的位数。
                  M的默认取值为10，D默认取值为0。不带参数时，等同decimal(10,0),带一个参数时，D取默认值。
                  M的取值范围为1~65，取0时会被设为默认值，超出范围会报错。
                  D的取值范围为0~30，而且必须<=M，超出范围会报错。
                  所以，很显然，当M=65，D=0时，可以取得最大和最小值。

DECIMAL(5,2),整数部分超出了范围（如上面的例子中，添加数值为1000.01），MySql就会报错，不允许存这样的值。
小数点部分若超出范围，就分以下情况：
    若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。如999.994实际被保存为999.99。
    若四舍五入后，整数部分超出范围，则MySql报错，并拒绝处理。如999.995和-999.995都会报错。

如果数据太大，可选方案1： 将小数，整数分开存储成2个整数，取和存都分开存储2个字段。可选方案2：存储成字符串。

所有算术都是使用有符号 BIGINT 或 DOUBLE 值完成的，因此您不应使用大于 9223372036854775807（63 位）的无符号大整数，结果中的一些最后数字可能是错误的，因为在将 BIGINT 值转换为 DOUBLE 时会出现舍入错误。
使用字符串存储 BIGINT 列中存储精确的整数值。在这种情况下，MySQL 执行字符串到数字的转换，不涉及中间双精度表示。

float(M,D): 一共M位整数，其中D位位于小数点后面。假如超过D位则四舍五入，即1.233四舍五入为1.23，1.237四舍五入为1.24。
FLOAT和DOUBLE中的M和D的取值默认都为0，会按照实际的精度来处理。
FLOAT和DOUBLE中如果M和D都有明确定义，其超出范围后的处理同decimal。

M取值范围为0~255。FLOAT只保证6位有效数字的准确性，所以FLOAT(M,D)中，M<=6时，数字通常是准确的。
double只保证16位有效数字的准确性，所以DOUBLE(M,D)中，M<=16时，数字通常是准确的。

    mysql> CREATE TABLE t (b BIT(8));
    mysql> INSERT INTO t SET b = b'11111111';
    mysql> INSERT INTO t SET b = b'1010';
    mysql> INSERT INTO t SET b = b'0101';
    mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;
    +------+----------+----------+----------+
    | b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
    +------+----------+----------+----------+
    |  255 | 11111111 | 377      | FF       |
    |   10 | 1010     | 12       | A        |
    |    5 | 101      | 5        | 5        |
    +------+----------+----------+----------+

MySQL 允许使用非标准语法：FLOAT（M，D） 或 REAL（M，D） 或 DOUBLE PRECISION（M，D），这里，（M，D） 表示值总共最多可以存储 M 位，其中 D 位可以位于小数点后。例如，定义为 FLOAT（7,4） 的列显示为 -999.9999。MySQL 在存储值时执行舍入，因此如果将 999.00009 插入 FLOAT（7,4） 列，则近似结果为 999.0001。

2、字符串

    MySQL 提供了8个基本的字符串类型,分别:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和SET等多种字符串类型。
对于字符串列（CHAR、VARCHAR和TEXT类型）的定义，MySQL 以字符定义长度。对于二进制字符串列（BINARY、VARBINARY和 BLOB 类型）的定义，MySQL 以字节定义长度。CHAR（0） 也非常有用：定义为 CHAR（0）则只能接受值 NULL 和 '' （空字符串)
BLOB最大长度为 65,535 （216 − 1） 字节，text最大长度为 65,535 （216 − 1） 个字符。

      字符串类型     范围                      描述及存储需求
        CHAR(M)       最多255个字符                   定长字符串
        VARCHAR(M)    最多65535个字符                 变长字符串
        BINARY(M)                                     定长二进制字符串
        VARBINARY                                    变长二进制字符串
        TINYBLOB     可变长度，最多255个字符        不超过 255 个字符的二进制字符串
        TINYTEXT     可变长度，最多255个字符        短文本字符串
        BLOB         可变长度，最多65535个字符      二进制形式的长文本数据
        TEXT         可变长度，最多65535个字符      长文本数据
        MEDIUMBLOB   最多0-16777215个字符          二进制形式的中等长度文本数据
        MEDIUMTEXT   最多0-16777215个字符          中等长度文本数据
        LOGNGBLOB    最多0-4294967295个字符        二进制形式的极大文本数据
        LONGTEXT     最多0-4294967295个字符        极大文本数据

Value       CHAR(4)     所需存储   VARCHAR(4)     所需存储
''              '    '        4 bytes     ''               1 byte
'ab'            'ab  '         4 bytes     'ab'          3 bytes
'abcd'          'abcd'       4 bytes     'abcd'        5 bytes
'abcdefgh'  'abcd'       4 bytes     'abcd'        5 bytes

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
3.varchar为可变长度字符串，在utf8编码的数据库中其长度范围为0~21844。

MySQL要求一个行的定义长度不能超过65535即64K
    （1）对于未指定varchar字段not null的字段，会有1个字节专门表示该字段是否为null。
    （2）varchar(M)，当M范围为0<=M<=255时会专门有一个字节记录varchar型字符串长度，当M>255时会专门有两个字节记录varchar型字符串的长度，把这一点和上一点结合，那么65535个字节实际可用的为65535-3=65532个字节
    （3）所有英文无论其编码方式，都占用1个字节，但对于gbk编码，一个汉字占两个字节，因此最大M=65532/2=32766；对于utf8编码，一个汉字占3个字节，因此最大M=65532/3=21844。
    （4）对于utfmb4编码方式，1个字符最大可能占4个字节，那么varchar(M)，M最大为65532/4=16383。
同样的，上面是表中只有varchar型数据的情况，如果表中同时存在int、double、char这些数据，需要把这些数据所占据的空间减去，才能计算varchar(M)型数据M最大等于多少。

text和varchar是一组既有区别又有联系的数据类型，其联系在于当varchar(M)的M大于某些数值时，varchar会自动转为text：

    M>255时转为tinytext
    M>500时转为text
    M>20000时转为mediumtext

所以过大的内容varchar和text没有区别，同事varchar(M)和text的区别在于：

    单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来
    text可以指定text(M)，但是M无论等于多少都没有影响
    text不允许有默认值，varchar允许有默认值。
    varchar和text两种数据类型，使用建议是能用varchar就用varchar而不用text（存储效率高），varchar(M)的M有长度限制，之前说过，如果大于限制，可以使用mediumtext（16M）或者longtext（4G）。

至于text和blob，text存储的是字符串而blob存储的是二进制字符串，简单说blob是用于存储例如图片、音视频这种文件的二进制数据的。
ENUM 列最多可以有 65,535 个不同的元素。

3、日期和时间类型
表示时态值的日期和时间数据类型为 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。每个时间类型都有一个有效值范围，以及一个“零”值，当您指定 MySQL 无法表示的无效值时，可以使用该值。
2位的年份，MySQL默认70-99 范围内的年份值变为 1970-1999。00-69 范围内的年份值将变为 2000-2069。
默认情况下，当 MySQL 遇到日期或时间类型的值超出范围或对该类型无效时，它会将该值转换为该类型的“零”值。例外情况是，超出范围的 TIME 值被剪切到 TIME 范围的相应端点。

     类型     大小(字节)      范围                               格式          用途
     DATE       4        1000-01-01/9999-12-31                   YYYY-MM-DD    日期值
     TIME       3        '-838:59:59'/'838:59:59'                HH:MM:SS      时间值或持续时间
     YEAR       1         1901/2155                              YYYY          年份值
     DATETIME   8     1000-01-01 00:00:00/9999-12-31 23:59:59    YYYY-MM-DD HH:MM:SS 混合日期和时间值
     TIMESTAMP  4     1970-01-01 00:00:00/2038-01-19 03:14:07    YYYYMMDD HHMMSS 混合日期和时间值，时间戳

    MySQL在5.6.4版本之后，TIMESTAMP和DATETIME支持到微秒。
    TIMESTAMP会根据系统时区进行转换，DATETIME则不会，一般使用TIMESTAMP国际化

 MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

        MySQL datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。
        select datediff('2008-08-08', '2008-08-01'); -- 7
        select datediff('2008-08-01', '2008-08-08'); -- -7

        MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。
        select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
        select timediff('08:08:08', '00:00:00'); -- 08:08:08

注意：timediff(time1,time2) 函数的两个参数类型必须相同。
此外，如果 explicit_defaults_for_timestamp 系统变量被禁用，则可以通过为其分配 NULL 值来将任何 TIMESTAMP（而不是 DATETIME）列初始化或更新为当前日期和时间，除非已使用 NULL 属性定义该列以允许 NULL 值。

列定义中使用 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP 子句。CURRENT_TIMESTAMP  CURRENT_TIMESTAMP（）、NOW（）、LOCALTIME、LOCALTIME（）、LOCALTIMESTAMP和 LOCALTIMESTAMP（）相同。

TIMESTAMP 的默认值为 0，除非使用 NULL 属性定义，在这种情况下，默认值为 NULL。
CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);

除非使用 NOT NULL 属性定义，否则 DATETIME 的默认值为 NULL，not null定义下默认值为 0。
CREATE TABLE t1 (
  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL
  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);

如果 TIMESTAMP 或 DATETIME 列定义在任何地方都包含显式的小数秒精度值，则必须在整个列定义中使用相同的值。这是允许的：
CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

不允许这样做：
CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

MySQL 对 TIME、DATETIME 和 TIMESTAMP 值提供秒的小数部分支持，精度高达微秒（6 位）：
CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
INSERT INTO fractest VALUES
('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

时间值将插入到表中，并进行四舍五入：发生此类舍入时，不会给出警告或错误。
mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
+-------------+------------------------+------------------------+

要改为插入带有截断的值，请启用 TIME_TRUNCATE_FRACTIONAL SQL 模式：
SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');

mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.77 | 2018-09-08 17:51:04.77 | 2018-09-08 17:51:04.77 |
+-------------+------------------------+------------------------+

================================= 8.0 空间+json ==================
8.0的新类型
空间类型：geometry point linestring polygon
空间集合：geometrycollection  multipoint multilinestring multipolygon

GEOMETRY 可以存储任何类型的几何值。其他单值类型（POINT、LINESTRING 和 POLYGON）将其值限制为特定的几何类型。
GEOMETRYCOLLECTION 可以存储任何类型的对象集合。其他集合类型（MULTIPOINT、MULTILINESTRING 和 MULTIPOLYGON）将集合成员限制为具有特定几何类型的集合成员。

CREATE TABLE geom (
    p POINT SRID 0,
    g GEOMETRY NOT NULL SRID 4326
);
如果空间列为 NOT NULL 并且具有特定的 SRID，则可以在空间列上创建 SPATIAL 索引.

JSON类型： json

存储 JSON 文档所需的空间与 LONGBLOB 或 LONGTEXT 大致相同;存储在 JSON 列中的任何 JSON 文档的大小都限制为 max_allowed_packet 系统变量的值。

详细请看：mysql 8.0 新增数据类型 json