MySQL-数据类型（一）

偏执的太偏执、 2022-11-05 04:28 238阅读 0赞

MySQL支持丰富的数据类型，总体上可以分为数值类型、日期和时间类型、字符串类型；数值类型包括整数类型、浮点类型和定点数类型；字符串类型包括文本字符串类型和二进制字符串类型

### 目录 ###

*   *   *   *  1.数值类型
             *   *  1.1整数类型
                 *  1.2浮点数类型
                 *  1.3定点数类型
             *  2.日期和时间类型
             *   *  2.1 year类型
                 *  2.2 time类型

#### 1.数值类型 ####

##### 1.1整数类型 #####

整数类型包括及存储空间取值范围：

<table> 
 <thead> 
  <tr> 
   <th>整数类型</th> 
   <th>说明</th> 
   <th>存储空间</th> 
   <th>有无符号</th> 
   <th>最小值</th> 
   <th>最大值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinyint</td> 
   <td>非常小的整数</td> 
   <td>1 字节</td> 
   <td>有</td> 
   <td>-128</td> 
   <td>127</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>无</td> 
   <td>0</td> 
   <td>255</td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td>小整数</td> 
   <td>2 byte</td> 
   <td>y</td> 
   <td>-32768</td> 
   <td>32767</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>n</td> 
   <td>0</td> 
   <td>65535</td> 
  </tr> 
  <tr> 
   <td>mediumint</td> 
   <td>中型大小的整数</td> 
   <td>3 byte</td> 
   <td>y</td> 
   <td>-8300608</td> 
   <td>8300607</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>n</td> 
   <td>0</td> 
   <td>16777215</td> 
  </tr> 
  <tr> 
   <td>int</td> 
   <td>一般大小的整数</td> 
   <td>4 byte</td> 
   <td>y</td> 
   <td>-2147483648</td> 
   <td>2147483647</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>n</td> 
   <td>0</td> 
   <td>4294967295</td> 
  </tr> 
  <tr> 
   <td>bigint</td> 
   <td>很大的整数</td> 
   <td>8字节</td> 
   <td>y</td> 
   <td>-9223372036854775808</td> 
   <td>9223372036854775807</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>n</td> 
   <td>0</td> 
   <td>18446744073709551615</td> 
  </tr> 
 </tbody> 
</table>

*  这里做个示例：

mysql> create table ngb(id int(5));
    Query OK, 0 rows affected, 1 warning (0.02 sec)
    
    mysql> show create table ngb \G
    *************************** 1. row ***************************
           Table: ngb
    Create Table: CREATE TABLE `ngb` (
      `id` int DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1 row in set (0.00 sec)

创建表ngb并设置显示宽度为5，下面插入一条数据超过显示宽度5：

mysql> insert into ngb(id) values (1),(123456);
    Query OK, 2 rows affected (0.02 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    mysql> SELECT *FROM ngb;
    +--------+
    | id     |
    +--------+
    |      1 |
    | 123456 |
    +--------+
    2 rows in set (0.00 sec)

这里看到括号里使用宽度限制但仍然可以正确的显示，因为8.0版本变化的原因，这里引用zero属性，表示数字的显示位数不够时，可以使用字符0进行填充：

mysql> create table ngb_1(id int zerofill,id2 int(5) zerofill);
    Query OK, 0 rows affected, 3 warnings (0.01 sec)
    
    mysql> insert into ngb_1(id,id2) values(1,1);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from ngb_1;
    +------------+-------+
    | id         | id2   |
    +------------+-------+
    | 0000000001 | 00001 |
    +------------+-------+
    1 row in set (0.00 sec)

当插入的数据小于设置的显示宽度时，会使用字符0进行填充  
再次向数据表ngb\_1插入数据：

mysql> insert into ngb_1(id,id2) values(1,12345678);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from ngb_1 \G
    *************************** 1. row ***************************
     id: 0000000001
    id2: 00001
    *************************** 2. row ***************************
     id: 0000000001
    id2: 12345678
    2 rows in set (0.00 sec)

当插入的数据超过了设置显宽时，仍可以正确的显示  
小结：插入的数据超过了设置显示宽度仍可以正确的显示宽度（不超过取值范围都可以）

mysql> show create table ngb_1 \G
    *************************** 1. row ***************************
           Table: ngb_1
    Create Table: CREATE TABLE `ngb_1` (
      `id` int(10) unsigned zerofill DEFAULT NULL,
      `id2` int(5) unsigned zerofill DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1 row in set (0.00 sec)

所有整数型都有一个可选的属性unsigned(无符号属性)，无符号整数类型的最小值取值为0，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型；如果在MySQL中创建数据表时，指定数据字段为zerofill,则MySQL会自动为当前列添加unsigned属性。  
在MySQL中，整数类型还有一个属性是auto\_increment,值一般从1开始，每行自动+1，如果在此属性的整数列插入null,则MySQL会在此列中插入一个比该列当前最大值大1的数值。  
一个表中最多只能有一个列被设置为auto\_increment,并且设置auto\_increment的列需要定义为not null，并且定义为primary key,或定义为not null并且定义为unique。

*  示例：

mysql> create table ngb_2(
        -> id int not null auto_increment primary key,
        -> age int
        -> );
    Query OK, 0 rows affected (0.01 sec)
    或
    mysql> create table ngb_5(
        -> id int not null auto_increment,
        -> age int,
        -> primary key(id)
        -> );
    Query OK, 0 rows affected (0.01 sec)

*  插入数据：

mysql> insert into ngb_2 (age) values(7),(18),(21);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

*  查看数据：

mysql> select * from ngb_2;
    +----+------+
    | id | age  |
    +----+------+
    |  1 |    7 |
    |  2 |   18 |
    |  3 |   21 |
    +----+------+
    3 rows in set (0.00 sec)

MySQL自动为auto\_increment类型的整型列设置了自动递增的整数值

*  将设置为auto\_increment的整型列定义为unique：

mysql> create table ngb_3(
        -> id int not null auto_increment unique,
        -> age int
        -> );
    Query OK, 0 rows affected (0.02 sec)
    mysql> insert into ngb_3 (age) values(11),(12),(10);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from ngb_3;
    +----+------+
    | id | age  |
    +----+------+
    |  1 |   11 |
    |  2 |   12 |
    |  3 |   10 |
    +----+------+
    3 rows in set (0.00 sec)

##### 1.2浮点数类型 #####

浮点数类型主要有两种：单精度浮点数float和双精度浮点数double如下：

<table> 
 <thead> 
  <tr> 
   <th>浮点数类型</th> 
   <th>说明</th> 
   <th>存储空间</th> 
   <th>有无符号</th> 
   <th>最小值</th> 
   <th>最大值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>float</td> 
   <td>单精度浮点数</td> 
   <td>4 byte</td> 
   <td>y</td> 
   <td>-3.402823466E+38</td> 
   <td>-1.175494351E-38</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>n</td> 
   <td>0和1.175494351E-38</td> 
   <td>3.402823466E+38</td> 
  </tr> 
  <tr> 
   <td>double</td> 
   <td>双精度浮点数</td> 
   <td>8 byte</td> 
   <td>y</td> 
   <td>-1.7976931348623157E+308</td> 
   <td>-2.2250738585072014E-308</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>n</td> 
   <td>0和2.2250738585072014E-308</td> 
   <td>1.7976931348623157E+308</td> 
  </tr> 
 </tbody> 
</table>

*  不同类型的浮点数的取值范围不同，相同类型的浮点数，有符号和无符号也不同
 *  浮点数类型中的float和double类型在不指定的精度时默认按照实际计算机硬件和操作系统决定的数据精度进行显示；如果指定的精度超出了浮点数类型的数据精度，则MySQL会自动进行四舍五入操作
 *  示例：

mysql> create table ngb_4(f float,d double);
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert into ngb_4 (f,d) values (3.14,6.66);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from ngb_4;
    +------+------+
    | f    | d    |
    +------+------+
    | 3.14 | 6.66 |
    +------+------+
    1 row in set (0.01 sec)

可以看到，能够正确地插入并显示数据

*  再次插入数据：

mysql> insert into ngb_4 (f,d) values (3.14444444444444,6.66666666666666666666);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from ngb_4;
    +---------+-------------------+
    | f       | d                 |
    +---------+-------------------+
    |    3.14 |              6.66 |
    | 3.14444 | 6.666666666666667 |
    +---------+-------------------+
    2 rows in set (0.01 sec)

可以看到，再次为float和double类型插入超出数据类型精度的数据时，MySQL对插入的数据进行四舍五入处理

*  精度显示设置：（A,B）表示当前数值包含整数位和小数位，A被称为精度，整数和小数一共会显示A位数字；B为标度，小数点后显示B位数字

mysql> create table ngb_6(
        -> f float(5,2),
        -> d double(4,1)
        -> );
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
    
    mysql> insert into  ngb_6 (f,d) values (3.14,3.14);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from ngb_6;
    +------+------+
    | f    | d    |
    +------+------+
    | 3.14 |  3.1 |
    +------+------+
    1 row in set (0.00 sec)

可以看到，这里的d设置（4，1）小数点后显示一位，即正常

##### 1.3定点数类型 #####

*  MySQL中的定点数类型只有decimal一种类型，也可以使用（A,B）进行表示，A为精度是数据的总位数；B为标度是数据的小数部分所占的位数
 *  定点数在MySQL内部是以字符串的形式进行存储，精度比浮点数更加精确，适合存储表示金额等需要高精度的数据
 *  decimal(A,B)类型的数据最大值取值范围与double类型不同，但是有效的数据范围是由A和B共同决定的,decimal的存储空间不是固定的，由精度值A决定，，总共占用的存储空间为A+2个字节。
 *  使用定点数类型表示数据时，当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入
 *  decimal类型不指定精度和标度时，默认decimal（10,0）
 *  示例：

mysql> create table ngb_7(
        -> d1 decimal,
        -> d2 decimal(5,3)
        -> );
    Query OK, 0 rows affected (0.03 sec)
    mysql> show create table ngb_7 \G
    *************************** 1. row ***************************
           Table: ngb_7
    Create Table: CREATE TABLE `ngb_7` (
      `d1` decimal(10,0) DEFAULT NULL,
      `d2` decimal(5,3) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1 row in set (0.00 sec)

*  插入数据：

mysql> insert into ngb_7 (d1,d2) values (3.14,6.66);
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    mysql> show warnings;
    +-------+------+-----------------------------------------+
    | Level | Code | Message                                 |
    +-------+------+-----------------------------------------+
    | Note  | 1265 | Data truncated for column 'd1' at row 1 |
    +-------+------+-----------------------------------------+
    1 row in set (0.00 sec)
    mysql> select * from ngb_7;
    +------+-------+
    | d1   | d2    |
    +------+-------+
    |    3 | 6.660 |
    |    3 | 6.660 |
    +------+-------+
    2 rows in set (0.00 sec)

这里有一个警告，显示d1字段的值被截断了，并且超过了精度值小数位超出了标度的限制，则会被拦截处理

*  插入数据：

mysql> insert into ngb_7 (d1,d2) values (1111111111111111111,6.66);
    ERROR 1264 (22003): Out of range value for column 'd1' at row 1

可以看到，插入超出精度和标度的值MySQL报错

#### 2.日期和时间类型 ####

主要有：year,time,date,datetime,timestamp

<table> 
 <thead> 
  <tr> 
   <th>日期/时间类型</th> 
   <th>说明</th> 
   <th>存储需求</th> 
   <th>格式</th> 
   <th>最小值</th> 
   <th>最大值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>year</td> 
   <td>年</td> 
   <td>1 byte</td> 
   <td>YYYY</td> 
   <td>1901</td> 
   <td>2155</td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>时间</td> 
   <td>3 byte</td> 
   <td>HH:MM:SS</td> 
   <td>-838:59:59</td> 
   <td>838:59:59</td> 
  </tr> 
  <tr> 
   <td>date</td> 
   <td>日期</td> 
   <td>3 byte</td> 
   <td>YYYY-MM-DD</td> 
   <td>1000-01-01</td> 
   <td>9999-12-03</td> 
  </tr> 
  <tr> 
   <td>datetime</td> 
   <td>日期时间</td> 
   <td>8 byte</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>1000-01-01 00:00:00</td> 
   <td>9999-12-31 23:59:59</td> 
  </tr> 
  <tr> 
   <td>timestamp</td> 
   <td>日期时间</td> 
   <td>4 byte</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>1970-01-01 00:00:01 UTC</td> 
   <td>2038-01-19</td> 
  </tr> 
  <tr> 
   <td>日期/时间类型</td> 
   <td>零值表示</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>–</td> 
   <td>–</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>year</td> 
   <td>0000</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>00:00:00</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

##### 2.1 year类型 #####

*  以4位字符串或数字格式表示year类型，格式位yyyy，最小值1901，最大值2155
 *  以2位字符串格式表示的year类型，最小值位00，最大值为99；当取值为00到99时，表示2000~2069；当取值为70到99时，表示1970 ~1999，如果插入的数据超出了取值范围，则MySQL会将值自动转换为2000
 *  以2位数字格式表示year类型，最小值为1，最大值为99；取值为1到69时，表示2001 ~2069，当取值为70 ~99时，表示1970 ~1999
 *  示例：分别插入数字2021和字符串2021

mysql> create table ngb_8(
        -> y year
        -> );
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into ngb_8 (y) values (2021),('2021');
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from ngb_8;
    +------+
    | y    |
    +------+
    | 2021 |
    | 2021 |
    +------+
    2 rows in set (0.00 sec)

*  再次插入数据字符串2156可以看到，超过了取值范围的报错：

mysql> insert into ngb_8 (y) values ('2156');
    ERROR 1264 (22003): Out of range value for column 'y' at row 1
    mysql>

*  插入两位字符串0，00，88，21：

mysql> insert into ngb_8 (y) values ('0'),('00'),('88'),('21');
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    mysql> select * from ngb_8;
    +------+
    | y    |
    +------+
    | 2021 |
    | 2021 |
    | 2000 | //0
    | 2000 | //00
    | 1988 | //88
    | 2021 | //21
    +------+
    6 rows in set (0.00 sec)

*  清除ngb\_8的数据，插入数字0，00，88，21：

mysql> delete from ngb_8;
    Query OK, 6 rows affected (0.00 sec)
    
    mysql> insert into ngb_8 (y) values (0),(00),(88),(21);
    Query OK, 4 rows affected (0.01 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    mysql> select * from ngb_8;
    +------+
    | y    |
    +------+
    | 0000 | //0
    | 0000 | //00
    | 1988 | //88
    | 2021 | //21
    +------+
    4 rows in set (0.00 sec)

以上可以看出，在输入的值在范围内，都可以正确的显示

##### 2.2 time类型 #####

*  将硬件时钟调整为与本地时钟一致，0为设置为UTC时间： `timedatectl set-local-rtc 1`  
    列出所有时区：`timedatectl list-timezones`  
    设置系统时区为上海:`timedatectl set-timezone Asia/Shanghai`
 *  time类型不仅可以表示时间，也包含日期部分，在MySQL中，需要3个字节的存储空间来存储
 *  格式：HH:MM:SS /*HH时、MM分、SS秒*/
 *  可以使用带冒号的字符串，如 D HH:MM:SS/HH:MM:SS/HH:MM、D HH:MM、D HH、SS格式,如果使用D格式的字符串插入time类型的字段时，D会被转化为小时，计算格式为D\*24+HH
 *  可以使用不带冒号的字符串或数字，格式为"HHMMSS"或者 HHMMSS；如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00：00：00进行存储
 *  使用current\_time或者NOW(),会插入当前系统的时间
 *  示例：

mysql> create table ngb_9(
        -> t time
        -> );
    Query OK, 0 rows affected (0.38 sec)
    
    mysql> insert into ngb_9 (t) values ('2 12:35:29'),('12:35:29'),('12:40'),('2 12:40'),('45');
    Query OK, 5 rows affected (0.02 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    mysql> select * from ngb_9;
    +----------+
    | t        |
    +----------+
    | 60:35:29 | //2 12:35:29
    | 12:35:29 | //12:35:29
    | 12:40:00 | //12:40
    | 60:40:00 | //2 12:40
    | 00:00:45 | //45
    +----------+
    5 rows in set (0.00 sec)

*  再次插入数据1：1：1，MySQL会自动补齐0

mysql> delete from ngb_9;
    Query OK, 5 rows affected (0.01 sec)
    
    mysql> select * from ngb_9;
    Empty set (0.00 sec)
    
    mysql> insert into ngb_9 (t) values ('1:1:1');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from ngb_9;
    +----------+
    | t        |
    +----------+
    | 01:01:01 |
    +----------+
    1 row in set (0.00 sec)

*  清除并插入新的数据：123456，124111，0（正确显示）

mysql> delete from ngb_9;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into ngb_9 (t) values ('123456'),('124111'),('0');
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> select * from ngb_9;
    +----------+
    | t        |
    +----------+
    | 12:34:56 |
    | 12:41:11 |
    | 00:00:00 |
    +----------+
    3 rows in set (0.01 sec)

*  插入系统当前时间：

mysql> insert into ngb_9 (t) values (now()),(current_time);
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from ngb_9;
    +----------+
    | t        |
    +----------+
    | 03:51:55 |
    | 03:51:55 |
    +----------+
    2 rows in set (0.00 sec)