MySQL数据类型

最后更新时间：2022年10月23日

整数类型

数值型数据类型主要用来存储数字，MySQL提供了多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，其所需要的存储空间也会越大。MySQL主要提供的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）、BIGINT。整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。

  • TINYINT：很小的整数，占用一字节存储空间。
  • SMALLINT：小的整数，占用两字节存储空间。
  • MEDIUMINT：中等大小的整数，占用三字节存储空间。
  • INT：普通大小的整数，占用四字节存储空间。
  • BIGINT：大整数，占用八字节存储空间。

注意以下语句

mysql> create table tmp
    -> (
    -> id int(10),
    -> name varchar(25)
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

id字段的数据类型为INT(10)，注意后面的数字10，它表示的是该数据类型指定的显示宽度，即能够显示的数值中数字的个数。显示宽度和数据类型的取值范围是无关的。显示宽度只是指明MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。其他整型数据类型也可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。

mysql> create table tmp
    -> (
    -> id int,
    -> id2 tinyint,
    -> id3 smallint,
    -> id4 mediumint,
    -> id5 bigint
    -> );
Query OK, 0 rows affected (0.01 sec)

浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。浮点数类型有两种：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）。定点数类型只有一种：DECIMAL。浮点数类型和定点数类型都可以用（M，N）来表示。其中，M称为精度，表示总共的位数；N称为标度，表示小数的位数。不论是定点数还是浮点数类型，如果用户指定的精度超出精度范围，则会四舍五入。

  • float：单精度浮点数，占四字节存储空间。

    • 有符号的取值范围:-3.402823466E+38 ~ -1.175494351E-38。
    • 无符号的取值范围：0和1.175494351E-38 ~ 3.402823466E+38

  • double：双精度浮点数，占八字节存储空间。

    • 有符号的取值范围：-1.7976931348623157E+308~-2.2250738585072014E-308。
    • 无符号的取值范围：0和2.2250738585072014E-308 ~1.7976931348623157E+308。
  • decimal(m,d)：压缩的严格定点数，占m+2字节

DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则其取值范围将随M的变大而变大。DECIMAL的存储空间并不是固定的，而由其精度值M决定的，占用M+2字节。

mysql> create table tmp
    -> (
    -> id int(3),
    -> name varchar(250),
    -> salary float(5,2)
    -> );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> insert into  tmp (id, name, salary) values (1, 'jack', 322.456), (2, 'ronie', 500.505);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+------+-------+--------+
| id   | name  | salary |
+------+-------+--------+
|    1 | jack  | 322.46 |
|    2 | ronie | 500.50 |
+------+-------+--------+
 2 rows in set (0.00 sec)

mysql>

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL的类型比较好，另外两个浮点数进行减法和比较运算时容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

日期与时间类型

MySQL中有多种表示日期的数据类型，主要有DATETIME、DATE、TIMESTAMP、TIME和YEAR。例如，当只记录年信息的时候，可以只使用YEAR类型，而没有必要使用DATE。每一个类型都有合法的取值范围，当指定确实不合法的值时系统将“零”值插入到数据库中。MySQL允许不严格语法：任何标点符号都可以用作日期部分或时间部分之间的间隔符。例如，98-12-31 11:30:45、98.12.3111+30+45、98/12/31 113045和98@12@31 11^30^45是等价的，这些值都可以正确地插入数据库。

  • YEAR：YEAR类型是一个单字节类型，用于表示年，在存储时只需要1字节。可以使用各种格式指定YEAR值。

    • 以4位字符串或者4位数字格式表示的YEAR，范围为1901～2155。输入格式为‘YYYY’或者YYYY。例如，输入‘2010’或2010，插入到数据库的值均为2010
    • 以2位字符串格式表示的YEAR，范围为‘00’到‘99’。‘00’～‘69’和‘70’～‘99’范围的值分别被转换为2000～2069和1970～1999范围的YEAR值。‘0’与‘00’的作用相同。插入超过取值范围的值将被转换为2000。
    • 以2位数字表示的YEAR，范围为1～99。1～69和70～99范围的值分别被转换为2001～2069和1970～1999范围的YEAR值。注意：在这里0值将被转换为0000，而不是2000。
    mysql> create table tmp
  -> (
  -> id int primary key auto_increment,
  -> name varchar(25),
  -> y year
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc tmp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(25) | YES  |     | NULL    |                |
| y     | year        | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
 3 rows in set (0.00 sec)

mysql> insert into tmp (name, y) values ('ronie', 2022);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tmp;
+----+-------+------+
| id | name  | y    |
+----+-------+------+
|  1 | ronie | 2022 |
+----+-------+------+
 1 row in set (0.00 sec)

mysql> insert into tmp (name, y) values ('kelly', 2199);
ERROR 1264 (22003): Out of range value for column 'y' at row 1

mysql> insert into tmp (name, y) values ('kelly', 22), ('jack', 20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+-------+------+
| id | name  | y    |
+----+-------+------+
|  1 | ronie | 2022 |
|  2 | kelly | 2022 |
|  3 | jack  | 2020 |
+----+-------+------+
 3 rows in set (0.00 sec)

mysql>

  • TIME：
    TIME类型用在只需要时间信息的值，在存储时需要3字节，格式为HH:MM:SS。其中，HH表示小时，MM表示分钟，SS表示秒。TIME类型的取值范围为-838:59:59 ～838:59:59，小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间（必须小于24小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可以大于24小时，或者甚至为负）。可以使用各种格式指定TIME值。

    • D HH:MM:SS格式的字符串。可以使用下面任何一种“非严格”的语法：HH:MM:SS、HH:MM、D HH:MM、D HH或SS。这里的D表示日，可以取0~34之间的值。在插入数据库时，D被转换为小时保存，格式为D*24+HH。
    • HHMMSS格式的、没有间隔符的字符串或者HHMMSS格式的数值，假定是有意义的时间。例如：101112被理解为10:11:12，但109712是不合法的（它有一个没有意义的分钟部分），存储时将变为00:00:00。
    • 如果没有冒号，MySQL解释值时，假定最右边的两位表示秒。1112表示11:12:00（11点12分），但MySQL将它们解释为00:11:12（11分12秒）。同样12被解释为00:00:12。相反，TIME值中如果使用冒号则肯定被看作当天的时间。也就是说，11:12表示11:12:00，而不是00:11:12。
    mysql> create table tmp(
  -> id int primary key auto_increment,
  -> name varchar(30),
  -> t time
  -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc tmp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
| t     | time        | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
 3 rows in set (0.00 sec)


mysql> insert into tmp (name, t) values ('ronie', '121200'), ('kelly', '1212'), ('jack', '12:12');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+-------+----------+
| id | name  | t        |
+----+-------+----------+
|  1 | ronie | 12:12:00 |
|  2 | kelly | 00:12:12 |
|  3 | jack  | 12:12:00 |
+----+-------+----------+
 3 rows in set (0.00 sec)

mysql> insert into tmp (name, t) values ('jerry', '2 12:12'), ('tom', '3 12');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+-------+----------+
| id | name  | t        |
+----+-------+----------+
|  1 | ronie | 12:12:00 |
|  2 | kelly | 00:12:12 |
|  3 | jack  | 12:12:00 |
|  6 | jerry | 60:12:00 |
|  7 | tom   | 84:00:00 |
+----+-------+----------+
 5 rows in set (0.00 sec)

  • DATE：
    DATE类型用在仅需要日期值时，没有时间部分，在存储时需要3字节。日期格式为‘YYYY-MM-DD’。其中，YYYY表示年，MM表示月，DD表示日。在给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入。

    • 以YYYY-MM-DD或者YYYYMMDD字符串格式表示的日期，取值范围为1000-01-01～9999-12-3。例如，输入2012-12-31或者20121231，插入数据库的日期都为2012-12-31。
    • 以YY-MM-DD或者YYMMDD数字格式表示的日期，与前面相似，00~69范围的年值转换为2000～2069，70～99范围的年值转换为1970～1999。例如，输入12-12-31插入数据库的日期为2012-12-31；输入981231，插入数据的日期为1998-12-31。
    • 以YY-MM-DD或者YYMMDD数字格式表示的日期，与前面相似，00~69范围的年值转换为2000～2069，70～99范围的年值转换为1970～1999。例如，输入12-12-31插入数据库的日期为2012-12-31；输入981231，插入数据的日期为1998-12-31。
    • 使用CURRENT_DATE或者NOW()，插入当前系统日期。
    mysql> desc tmp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
| t     | time        | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
 3 rows in set (0.00 sec)

mysql> alter table tmp add d date after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into tmp (name, t, d) values ('lucy', '23:00:00', '2022-6-30'), ('mike', '230000', '20200925');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+-------+------------+----------+
| id | name  | d          | t        |
+----+-------+------------+----------+
|  1 | ronie | NULL       | 12:12:00 |
|  2 | kelly | NULL       | 00:12:12 |
|  3 | jack  | NULL       | 12:12:00 |
|  6 | jerry | NULL       | 60:12:00 |
|  7 | tom   | NULL       | 84:00:00 |
|  8 | lucy  | 2022-06-30 | 23:00:00 |
|  9 | mike  | 2020-09-25 | 23:00:00 |
+----+-------+------------+----------+
 7 rows in set (0.00 sec)

mysql> insert into tmp (name, t, d) values ('tony', now(), current_date());
Query OK, 1 row affected (0.01 sec)

mysql> select * from tmp;
+----+-------+------------+----------+
| id | name  | d          | t        |
+----+-------+------------+----------+
|  1 | ronie | NULL       | 12:12:00 |
|  2 | kelly | NULL       | 00:12:12 |
|  3 | jack  | NULL       | 12:12:00 |
|  6 | jerry | NULL       | 60:12:00 |
|  7 | tom   | NULL       | 84:00:00 |
|  8 | lucy  | 2022-06-30 | 23:00:00 |
|  9 | mike  | 2020-09-25 | 23:00:00 |
| 10 | tony  | 2022-10-08 | 16:04:38 |
+----+-------+------------+----------+
 8 rows in set (0.00 sec)

  • DATETIME：
    DATETIME类型用于需要同时包含日期和时间信息的值，在存储时需要8字节。日期格式为YYYY-MM-DD HH:MM:SS。其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒。在给DATETIME类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入。

    • 以YYYY-MM-DD HH:MM:SS或者YYYYMMDDHHMMSS字符串格式表示的值，取值范围为1000-01-01 00:00:00～9999-12-323:59:59。例如，输入2012-12-31 05: 05: 05或者20121231050505，插入数据库的DATETIME值都为2012-12-31 05: 05:05。
    • 以YY-MM-DD HH:MM:SS或YYMMDDHHMMSS字符串格式表示的日期，在这里YY表示两位的年值。与前面相同，00～69范围的年值转换为2000～2069，70～99范围的年值转换为1970～1999。例如，输入12-12-31 05:05:05，插入数据库的DATETIME为2012-12-31 05: 05:05；输入980505050505，插入数据库的DATETIME为1998-05-05 05:05:05。
    • 以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。例如，输入20121231050505，插入数据库的DATETIME为2012-12-3105:05:05；输入981231050505，插入数据的DATETIME为1998-12-31 05: 05:05。
    mysql> create table tmp
  -> (
  -> id int primary key auto_increment,
  -> dt datetime,
  -> name varchar(25)
  -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc tmp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| dt    | datetime    | YES  |     | NULL    |                |
| name  | varchar(25) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
 3 rows in set (0.00 sec)

mysql> insert into tmp (name, dt) values ('ronie', '20201225134523'), ('jack', '2022-9-4 12:00:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+---------------------+-------+
| id | dt                  | name  |
+----+---------------------+-------+
|  1 | 2020-12-25 13:45:23 | ronie |
|  2 | 2022-09-04 12:00:00 | jack  |
+----+---------------------+-------+
 2 rows in set (0.00 sec)

mysql> insert into tmp (name, dt) values ('lucy', '701225134523');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tmp;
+----+---------------------+-------+
| id | dt                  | name  |
+----+---------------------+-------+
|  1 | 2020-12-25 13:45:23 | ronie |
|  2 | 2022-09-04 12:00:00 | jack  |
|  3 | 1970-12-25 13:45:23 | lucy  |
+----+---------------------+-------+
 3 rows in set (0.00 sec)

mysql>

  • TIMESTAMP：
    TIMESTAMP的显示格式与DATETIME相同，显示宽度固定在19个字符，日期格式为YYYY-MM-DD HH:MM:SS，在存储时需要4字节。TIMESTAMP列的取值范围小于DATETIME的取值范围，为‘1970-01-01 00:00:01’UTC～‘2038-01-1903:14:07’UTC。其中，UTC（Coordinated Universal Time）为世界标准时间，因此在插入数据时，要保证在合法的取值范围内。DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。

    mysql> alter table tmp add stamp timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tmp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| dt    | datetime    | YES  |     | NULL    |                |
| name  | varchar(25) | YES  |     | NULL    |                |
| stamp | timestamp   | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
 4 rows in set (0.00 sec)

mysql> insert into tmp (name, dt, stamp) values ('jerry', '991231134243', '950101000000');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tmp;
+----+---------------------+-------+---------------------+
| id | dt                  | name  | stamp               |
+----+---------------------+-------+---------------------+
|  1 | 2020-12-25 13:45:23 | ronie | NULL                |
|  2 | 2022-09-04 12:00:00 | jack  | NULL                |
|  3 | 1970-12-25 13:45:23 | lucy  | NULL                |
|  4 | 1999-12-31 13:42:43 | jerry | 1995-01-01 00:00:00 |
+----+---------------------+-------+---------------------+
 4 rows in set (0.00 sec)

mysql>

文本字符串类型

文本字符串可以进行区分或者不区分大小写的串比较，还可以进行模式匹配查找。在MySQL中，文本字符串类型是指CHAR、VARCHAR、TEXT、ENUM和SET。VARCHAR和TEXT类型都是变长类型，其存储需求取决于列值的实际长度（用L表示），而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的字符串，实际的存储需要是字符串的长度L加上1字节（记录字符串的长度）。对于字符abcd，L是4而存储要求是5字节。

  1. CHAR和VARCHAR类型

    • CHAR(M)：固定长度非二进制字符串，M字节，1≤M≤255。VARCHAR(M)：变长非二进制字符串，L+1字节，在此L≤M和1≤M≤255。

      • CHAR(M)为固定长度字符串，在定义时指定字符串列长。当保存时在右侧填充空格，以达到指定的长度。M表示列长度，M的范围是0~255个字符。例如，CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4。当检索到CHAR值时，尾部的空格将被删除。
      • VARCHAR(M)是长度可变的字符串，M表示最大列长度。M的范围是0~65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加1。
      mysql> create table tmp(
-> id int primary key auto_increment,
-> name varchar(255),
-> home_location char(255),
-> age tinyint default 22
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc tmp;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int          | NO   | PRI | NULL    | auto_increment |
| name          | varchar(255) | YES  |     | NULL    |                |
| home_location | char(255)    | YES  |     | NULL    |                |
| age           | tinyint      | YES  |     | 22      |                |
+---------------+--------------+------+-----+---------+----------------+
 4 rows in set (0.00 sec)

mysql> insert into tmp (name, home_location) values ('ronie ', 'jiangsu  '), ('jack', 'sichuan');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+--------+---------------+------+
| id | name   | home_location | age  |
+----+--------+---------------+------+
|  1 | ronie  | jiangsu       |   22 |
|  2 | jack   | sichuan       |   22 |
+----+--------+---------------+------+
 2 rows in set (0.00 sec)

mysql> select length(name), length(home_location) from tmp;
+--------------+-----------------------+
| length(name) | length(home_location) |
+--------------+-----------------------+
|            6 |                     7 |
|            4 |                     7 |
+--------------+-----------------------+
 2 rows in set (0.00 sec)

      注意到在查询时home_location末尾的字符串被截取了。

  2. TEXT类型

    • TINYTEXT：非常小的非二进制字符串，L+1字节，在此L<2^8，TINYTEXT最大长度为255（28–1）字符。
    • TEXT：小的非二进制字符串，L+2字节，在此L<2^ 16。TEXT最大长度为65535（216–1）字符
    • MEDIUMTEXT：中等大小的非二进制字符串，L+3字节，在此L<2^24。MEDIUMTEXT最大长度为16777215（224–1）字符。
    • LONGTEXT：大的非二进制字符串，L+4字节，在此L<2^32。LONGTEXT最大长度为4294967295（232–1）字符。

  3. ENUM类型
    枚举类型，只能有一个枚举字符串值，1或2字节，取决于枚举值的数目(最大值为65535)。ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值。语法：字段名 ENUM('值1','值2',...,'值n')。ENUM列总有一个默认值：如果将ENUM列声明为NULL，NULL值则为该列的一个有效值，并且默认值为NULL；如果ENUM列被声明为NOT NULL，其默认值为允许的值列表的第1个元素。

    mysql> create table tmp(
 -> id int primary key auto_increment,
 -> name varchar(25),
 -> level enum('best', 'good', 'bad')
 -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc tmp;
+-------+---------------------------+------+-----+---------+----------------+
| Field | Type                      | Null | Key | Default | Extra          |
+-------+---------------------------+------+-----+---------+----------------+
| id    | int                       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(25)               | YES  |     | NULL    |                |
| level | enum('best','good','bad') | YES  |     | NULL    |                |
+-------+---------------------------+------+-----+---------+----------------+
 3 rows in set (0.00 sec)

mysql> insert into tmp (name, level) values ('ronie', 'best'), ('kelly', 'good'), ('jack', bad);
ERROR 1054 (42S22): Unknown column 'bad' in 'field list'
mysql> insert into tmp (name, level) values ('ronie', 'best'), ('kelly', 'good'), ('jack', 'bad');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+-------+-------+
| id | name  | level |
+----+-------+-------+
|  1 | ronie | best  |
|  2 | kelly | good  |
|  3 | jack  | bad   |
+----+-------+-------+
 3 rows in set (0.00 sec)

  4. SET：个设置，字符串对象可以有零个或多个SET成员，占用1、 2、3、4或8字节，取决于集合成员的数量(最多为64个成员)。SET是一个字符串对象，可以有零或多个值。SET列最多可以有64个成员，其值为表创建时规定的一列值。指定包括多个SET成员的SET列值时，各成员之间用逗号(,)间隔开。语法格式如下：SET('值1','值2',...,'值n')。与ENUM类型相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET成员值的尾部空格将自动被删除。与ENUM类型不同的是，ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可从定义的列值中选择多个字符的联合。如果插入SET字段中列值有重复，则MySQL自动删除重复的值；插入SET字段的值的顺序并不重要，MySQL会在存入数据库时按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL将忽视这些值，并给出警告。

    mysql> create table tmp(
 -> id int primary key auto_increment,
 -> name varchar(25),
 -> level set('good', 'normal', 'bad')
 -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tmp (name, level) values ('ronie', 'good,normal'), ('kelly', 'bad');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+-------+-------------+
| id | name  | level       |
+----+-------+-------------+
|  1 | ronie | good,normal |
|  2 | kelly | bad         |
+----+-------+-------------+
 2 rows in set (0.00 sec)

mysql>

二进制字符串类型

MySQL中的二进制数据类型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

  1. BIT类型
    BIT(M)：位字段类型，大约(M+7)/8字节。BIT类型是位字段类型。M表示每个值的位数，范围为1~64。如果M被省略，默认为1。如果为BIT(M)列分配的值的长度小于M位，就在值的左边用0填充。

    mysql> create table tmp(
-> id int primary key auto_increment,
-> name varchar(25),
-> b bit(4)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc tmp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(25) | YES  |     | NULL    |                |
| b     | bit(4)      | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
 3 rows in set (0.00 sec)

mysql> insert into tmp (name, b) values ('ronie', 4), ('kelly', 15);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tmp (name, b) values ('lucy', 16);
ERROR 1406 (22001): Data too long for column 'b' at row 1

mysql> select * from tmp;
+----+-------+------------+
| id | name  | b          |
+----+-------+------------+
|  1 | ronie | 0x04       |
|  2 | kelly | 0x0F       |
+----+-------+------------+
 2 rows in set (0.00 sec)


mysql> select (b + 0) from tmp;
+---------+
| (b + 0) |
+---------+
|       4 |
|      15 |
+---------+
 2 rows in set (0.00 sec)

mysql> select bin((b + 0)) from tmp;
+--------------+
| bin((b + 0)) |
+--------------+
| 100          |
| 1111         |
+--------------+
 2 rows in set (0.00 sec)

  2. BINARY和VARBINARY类型
    BINARY(M)：固定长度二进制字符串，M字节。VARBINARY(M)：可变长度二进制字符串，M+1字节。BINARY类型的长度是固定的，指定长度之后，不足最大长度的，将在它们右边填充\0补齐以达到指定长度。例如：指定列数据类型为BINARY(3)，当插入a时，存储的内容实际为a\0\0，当插入ab时，实际存储的内容为ab\0，不管存储的内容是否达到指定的长度，其存储空间均为指定的值M。VARBINARY类型的长度是可变的，指定好长度之后，其长度可以在0到最大值之间。例如：指定列数据类型为VARBINARY(20)，如果插入的值的长度只有10，则实际存储空间为10加1，即实际占用的空间为字符串的实际长度加1。

    mysql> create table tmp(
 -> id int primary key auto_increment,
 -> b binary(4),
 -> vb varbinary(4)
 -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tmp (b, vb) values (10, 11), (12,13), (14,15);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+----+------------+------------+
| id | b          | vb         |
+----+------------+------------+
|  1 | 0x31300000 | 0x3131     |
|  2 | 0x31320000 | 0x3133     |
|  3 | 0x31340000 | 0x3135     |
+----+------------+------------+
 3 rows in set (0.00 sec)

mysql> select (b + 0) from tmp;
+---------+
| (b + 0) |
+---------+
|      10 |
|      12 |
|      14 |
+---------+
 3 rows in set, 3 warnings (0.00 sec)

mysql> select bin((b + 0)) from tmp;
+--------------+
| bin((b + 0)) |
+--------------+
| 1010         |
| 1100         |
| 1110         |
+--------------+
 3 rows in set, 3 warnings (0.00 sec)

  3. BLOB类型
    BLOB是一个二进制大对象，用来存储可变数量的数据。BLOB类型分为4种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，BLOB列存储的是二进制字符串（字节字符串），TEXT列存储的是非二进制字符串（字符字符串）。BLOB列没有字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。

    • TINYBLOB(M)：非常小的BLOB，L+1字节,在此L<2^8。
    • BLOB(M)：小BLOB，L+2字节，在此L<2^16。
    • MEDIUMBLOB(M)：中等大小的BLOB，L+3字节，在此L<2^24。
    • LONGBLOB(M)：非常大的BLOB，L+4字节,在此L<2^32。

声明：Hello World|版权所有，违者必究|如未注明，均为原创|本网站采用BY-NC-SA协议进行授权

转载：转载请注明原文链接 - MySQL数据类型

我的朋友，理论是灰色的，而生活之树是常青的！