（六）列类型

文章目录

列类型（数据类型）

所谓数据类型，从人的角度看，就是对数据进行统一的分类； 从系统的角度看，是为了 能使用的统一的方式进行管理，更好的利用有限的空间；

SQL 中将数据分为三大类：数值类型，字符串类型、时间日期类型 ；

但是三大类又进行了细致的分类 ：

在这里插入图片描述

数值型

数值型数据，都是数值，mysql 将数值型分为：整数型 和 小数型 ；

  • 整数型

    存放整形数据，在 mysql 中为了节省空间，又细致地将整数型划分了5类：

    型 说明 表示范围
    Tinyint 迷你整形，使用一个字节存储（1*8 位） 2的8次方
    Smallint 小整形，使用二个字节存储（2*8 位） 2的16次方
    Mediumint 中整形，使用三个字节存储（3*8 位） 2的24次方
    Int 整形，使用四个字节存储（4*8 位） 2的32次方
    Bigint 大整形，使用八个字节存储（8*8 位） 2的64次方

    SQL 默认数值型是有正负的，假如不想使用负数，则使用 unsigned 控制是否有正负 ；

    sql 演示：

    # 测试
mysql> create table if not exists int_test(
    -> int_tiny tinyint,
    -> int_smallint smallint,
    -> int_medium mediumint,
    -> int_int int,
    -> int_big bigint
    -> )charset utf8 ;
Query OK, 0 rows affected

#  查看
mysql> desc int_test ;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| int_tiny     | tinyint(4)   | YES  |     | NULL    |       |
| int_smallint | smallint(6)  | YES  |     | NULL    |       |
| int_medium   | mediumint(9) | YES  |     | NULL    |       |
| int_int      | int(11)      | YES  |     | NULL    |       |
| int_big      | bigint(20)   | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set

# 更改 int_tiny 为无符号类型
mysql> alter table int_test modify int_tiny tinyint unsigned ;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

# 再次查看
mysql> desc int_test ;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| int_tiny     | tinyint(3) unsigned | YES  |     | NULL    |       |
| int_smallint | smallint(6)         | YES  |     | NULL    |       |
| int_medium   | mediumint(9)        | YES  |     | NULL    |       |
| int_int      | int(11)             | YES  |     | NULL    |       |
| int_big      | bigint(20)          | YES  |     | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+
5 rows in set

    对于 desc 操作，显示的数据库表细节，发现在每个列类型的都有一个数字，那是代表 显示宽度；

    #插入一条数据
mysql> insert into int_test values(1,1,1,1,1);
Query OK, 1 row affected

#没有  zerofill 的时候
mysql> select * from int_test;
+----------+--------------+------------+---------+---------+
| int_tiny | int_smallint | int_medium | int_int | int_big |
+----------+--------------+------------+---------+---------+
|        1 |            1 |          1 |       1 |       1 |
+----------+--------------+------------+---------+---------+
1 row in set

    显示宽度需要与 zerofill（零填充） 配合使用，当数据长度不够宽度的时候，自动在前面 补0 ；当我们把把某列设为 zerofill 的时候，该列会被自动的设置为无符号 ；

    # 直接设置 zerofill  ，会自动的设置 unsigned
mysql> alter table int_test modify int_tiny tinyint zerofill;
Query OK, 1 row affected
Records: 1  Duplicates: 0  Warnings: 0

#  查看
mysql> desc int_test ;
+--------------+------------------------------+------+-----+---------+-------+
| Field        | Type                         | Null | Key | Default | Extra |
+--------------+------------------------------+------+-----+---------+-------+
| int_tiny     | tinyint(3) unsigned zerofill | YES  |     | NULL    |       |
| int_smallint | smallint(6)                  | YES  |     | NULL    |       |
| int_medium   | mediumint(9)                 | YES  |     | NULL    |       |
| int_int      | int(11)                      | YES  |     | NULL    |       |
| int_big      | bigint(20)                   | YES  |     | NULL    |       |
+--------------+------------------------------+------+-----+---------+-------+
5 rows in set

#再次查询数据，int_tiny 就会有0补在前面
mysql> select * from int_test;
+----------+--------------+------------+---------+---------+
| int_tiny | int_smallint | int_medium | int_int | int_big |
+----------+--------------+------------+---------+---------+
|       01 |            1 |          1 |       1 |       1 |
|       01 |            1 |          1 |       1 |       1 |
|       01 |            1 |          1 |       1 |       1 |
|       01 |            1 |          1 |       1 |       1 |
+----------+--------------+------------+---------+---------+
4 rows in set

    我们可以显示的申明显示宽度；

    mysql> alter table int_test modify int_tiny tinyint(2) zerofill;
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

  • 小数型

    小数型：带有小数点或者超出整形的数值类型；

    SQL 中，将小数型分为两种：浮点型和定点型；
    浮点型：小数点浮动，精度有限，且会丢失进度；
    定点型：小数点固定，精度固定，不会丢失精度；

    • 浮点型

      浮点型数据是一种精度型数据，因为超出指定范围之后，会丢失精度，也就是进行四舍五入；

      Float ：单精度，占用 4 个字节存储，精度大概是 7 位左右 ；
      Double ：双精度，占用 8 个字节存储，精度大概是 15 位左右 ；

      直接 float ：表示没有小数部分 ；
      float(M,D) ：M代表总长度，D代表小数长度，整数长度是 M-D ；

      mysql> create table if not exists float_test(
    -> f1 float ,
    -> f2 float(10,2),
    -> f3 float(6,2)
   ->  ) charset utf8 ;
Query OK, 0 rows affected

      插入数据的时候，可以使用 科学计数法 ；

      # 使用科学计数法进行插入
mysql> insert into float_test values(1222,9999.99,9.99e3);
Query OK, 1 row affected

mysql> select * from float_test ;
+------+-----------+------+
| f1   | f2        | f3   |
+------+-----------+------+
| 1222 |   9999.99 | 9990 |
+------+-----------+------+

      插入数据的时候，整数长度不能超过规定的长度，但是小数部分可以超过，系统会进行四舍五入 ，

      mysql> insert into float_test values(1222,9999.989,9.99e3);
Query OK, 1 row affected

mysql> select * from float_test ;
+------+-----------+------+
| f1   | f2        | f3   |
+------+-----------+------+
| 1222 |   9999.99 | 9990 |   -- 四舍五入
+------+-----------+------+
5 rows in set

      浮点数如果因为系统的进位导致整数部门的长度超过指定的长度，此时系统是允许其长度的 ；

      	mysql> insert into float_test values(1222,99999999.99,9.99e3);
	Query OK, 1 row affected
	
	mysql> select * from float_test ;
	+------+-----------+------+
	| f1   | f2        | f3   |
	+------+-----------+------+
	| 1222 | 100000000 | 9990 |   -- 由于超出进度，进行四舍五入，导致整数位长度 超过 指定长度
	+------+-----------+------+
	1 row in set

      浮点型的精度，float 大概是7位，double大概是15位；即使我们人为的强制提升也没有用；

      	# 人为的将float 设定为  （20,2）
	mysql> alter table float_test modify f2 float(20,2);
	Query OK, 12 rows affected
	Records: 12  Duplicates: 0  Warnings: 0
	
	mysql> desc float_test;
	+-------+-------------+------+-----+---------+-------+
	| Field | Type        | Null | Key | Default | Extra |
	+-------+-------------+------+-----+---------+-------+
	| f1    | float       | YES  |     | NULL    |       |
	| f2    | float(20,2) | YES  |     | NULL    |       |
	| f3    | float(6,2)  | YES  |     | NULL    |       |
	+-------+-------------+------+-----+---------+-------+
	3 rows in set

	# 插入数据
	mysql> insert into float_test values(33,12345678999.9,333.3);
	Query OK, 1 row affected

	# 查看，发现精度丢失
	mysql> select * from float_test;
	+------+----------------+-------+
	| f1   | f2             | f3    |
	+------+----------------+-------+
	|   33 | 12345678848.00 | 333.3 |
	+------+----------------+-------+

    • 定点型

      绝对的保证整数部分不会被四舍五入 （不会丢失精度），小数部分有可能 （理论上小数部分也不会丢失精度）；

      Decimal（M,D） ：变长类型，大致每9个数字 采用4个字节存储。整数和小数分开计算；M最大值是65，D最大值是30，默认是（10,0）；

      mysql> create table if not exists decimal_test(
    -> f1 float(10,2),
    -> d1 decimal
    -> )charset utf8 ;
Query OK, 0 rows affected

mysql> desc decimal_test;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| f1    | float(10,2)   | YES  |     | NULL    |       |
| d1    | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set

      插入数据的时候，定点数的整数部分是一定不能 超过长度的（进位也不可以） ，小数部分的长度可以随意的超出，但是插入的时候会产生警告！超出以后，系统会自动的四舍五入 ，注意四舍五入，不能让整数位超出了 ！

      定点型，只要整数长度在35位以内，都能保证其精度 ；

        	# 改为25
	mysql> alter table decimal_test modify d1 decimal(25,2) ;
	Query OK, 3 rows affected
	Records: 3  Duplicates: 0  Warnings: 0
	
	mysql> desc decimal_test;
	+-------+---------------+------+-----+---------+-------+
	| Field | Type          | Null | Key | Default | Extra |
	+-------+---------------+------+-----+---------+-------+
	| f1    | float(10,2)   | YES  |     | NULL    |       |
	| d1    | decimal(25,2) | YES  |     | NULL    |       |
	+-------+---------------+------+-----+---------+-------+
	2 rows in set

	mysql> insert into decimal_test values(9999.99,123456789999.99);
	Query OK, 1 row affected
	
	mysql> select * from decimal_test;
	+---------+-----------------+
	| f1      | d1              |
	+---------+-----------------+		
	| 9999.99 | 123456789999.99 |
	+---------+-----------------+

时间日期类型

类型 解释说明 格式 取值 0值 存储空间（字节）
Datetime 时间日期 YYYY-mm-dd HH-ii-ss ； 1000-01-01 00:00:00 —— 9999-12-31 23:59:59 0000-00-00 00:00:00 8
Date 日期，就是datetime 中的date 部分 YYYY-mm-dd 1000-01-01 —— 9999-12-31 0000-00-00 3
Time 时间（段），指定的某个区间之间，负时间到正时间 HH:MM:SS -838:59:59——838:59:59 00:00:00 3
Timestamp 时间戳，并不是真正的时间戳，只是从 1970 年开始的 YYYY-mm-dd HH-ii-ss 格式 YYYY-mm-dd HH-ii-ss 1970-01-01 00:00:00——2038-01-19 03:14:07 0000-00-00 00:00:00 4
Year 年份，有两种形式，year（2）和 year（4） YYYY 1901-2156 0000 1 
mysql> create table if not exists date_test(
    -> t1 datetime,
    -> t2 date,
    -> t3 time,
    -> t4 year,
    -> t5 timestamp
    -> ) charset utf8 ;
Query OK, 0 rows affected

mysql> desc date_test ;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| t1    | datetime  | YES  |     | NULL              |       |
| t2    | date      | YES  |     | NULL              |       |
| t3    | time      | YES  |     | NULL              |       |
| t4    | year(4)   | YES  |     | NULL              |       |  -- year 默认的是 year (4)
| t5    | timestamp | YES  |     | CURRENT_TIMESTAMP |       |  -- 时间戳有个默认值
+-------+-----------+------+-----+-------------------+-------+
5 rows in set

time 可以是负数，当为负数的时候，表示一个 时间段 ，从过去的某个时间到现在

# time 为负数，
mysql> insert into date_test values("1997-11-22 10:22:22","1997-01-22","-11:22:22","18","1997-01-22 12:22:23");
Query OK, 1 row affected


# time 为负数，但是前面多个数字，表示 - (2* 24 + 11)
mysql> insert into date_test values("1997-11-22 10:22:22","1997-01-22","-2 11:22:22","18","1997-01-22 12:22:23");
Query OK, 1 row affected

# 从结果中可以看出，前面多个数字的时候，表示 - (数字* 24 + 11)
mysql> select * from date_test ;
+---------------------+------------+-----------+------+---------------------+
| t1                  | t2         | t3        | t4   | t5                  |
+---------------------+------------+-----------+------+---------------------+
| 1997-11-22 10:22:22 | 1997-01-22 | -11:22:22 | 2018 | 1997-01-22 12:22:23 |
| 1997-11-22 10:22:22 | 1997-01-22 | -59:22:22 | 2018 | 1997-01-22 12:22:23 |
+---------------------+------------+-----------+------+---------------------+
2 rows in set

year 可以使用2位数插入，也可以使用4位数插入；
用2位数插入的时候，表示的时间段为 1970 - 2069 ，也就是 30 ，代表2030 ，90 则代表1990；

mysql> insert into date_test values("1997-11-22 10:22:22","1997-01-22","-11:22:22","80","1997-01-22 12:22:23");
Query OK, 1 row affected

mysql> insert into date_test values("1997-11-22 10:22:22","1997-01-22","-11:22:22","69","1997-01-22 12:22:23");
Query OK, 1 row affected

mysql> insert into date_test values("1997-11-22 10:22:22","1997-01-22","-11:22:22","70","1997-01-22 12:22:23");
Query OK, 1 row affected

mysql> insert into date_test values("1997-11-22 10:22:22","1997-01-22","-11:22:22","18","1997-01-22 12:22:23");
Query OK, 1 row affected

mysql> select * from date_test ;
+---------------------+------------+-----------+------+---------------------+
| t1                  | t2         | t3        | t4   | t5                  |
+---------------------+------------+-----------+------+---------------------+
| 1997-11-22 10:22:22 | 1997-01-22 | -11:22:22 | 1980 | 1997-01-22 12:22:23 |
| 1997-11-22 10:22:22 | 1997-01-22 | -11:22:22 | 2069 | 1997-01-22 12:22:23 |
| 1997-11-22 10:22:22 | 1997-01-22 | -11:22:22 | 1970 | 1997-01-22 12:22:23 |
| 1997-11-22 10:22:22 | 1997-01-22 | -11:22:22 | 2018 | 1997-01-22 12:22:23 |
+---------------------+------------+-----------+------+---------------------+
5 rows in set

timestamp 有个默认值，就是当前系统的时间，每次更新记录，该记录的时间戳会被自动更新 ；

想要拿到真正的 时间戳 ，使用 mysql 的函数 ：unix_timestamp()

# 获取真正的时间戳
mysql> select unix_timestamp() ;
+------------------+
| unix_timestamp() |
+------------------+
|       1538123513 |
+------------------+
1 row in set

字符串类型

在 sql 中，将字符串类型分为 6 类 char、varchar、text、blob、enum、set ;

  1. 定长字符串（char）

    磁盘（二维表）在定义结构的时候，就已经明确最终数据的存储长度 ；

    Char（L） ：L 代表 length ，可以存储的长度，单位为 字符 ， 不是字节（在低版本的mysql 是字节，以 5.0 为分割） ，最大长度为 255 ；

    例子：char (4) ，在 UTF8 环境下，占用字节：4*3 = 12 ； （UTFB中一个汉字占用3个字节）

  2. 变长字符串（varchar）

    在分配空间的时候，按照最大的空间分配，但是实际上最终用了多少，是根据具体的数据来确定的 ，起到节省空间的效果。

    Varchar（L） : L 表示字符长度，理论长度是 65536 个字符，但是会多存储1或者2个字节来确定存储的实际长度 ；

    具体是1 还是 2，看实际存储的长度，以 255 为分界线，小于 255 使用 1个字节（2的8次方，是255，可以记录下实际长度），就足够保存了，大于 255 的使用 2个字节（2的16次方 是65535，可以记录它的最大长度了）就足以保存了。

    例子：varchar(10) ：假如存满了10个字符，则占用 10 * 3 + 1 = 31 ; 如果只保存了 3 个字符，则占用 3 3 +1 = 10 个字节 ；

    1. char 和 varchar 的区别：

      char : 定长字符串，空间有时候会浪费，但是效率高，不需要去计算实际占用多少空间，一般用在存储的数据长度固定，如身份证、手机号；

      varchar：变长字符串，不会浪费空间，但是效率低，每次都要去计算实际占用的空间，一般存储名字等长度不固定的；

  3. 文本字符串（Text、Blob）

    一般存储的字符超过255以后，我们是不会选择 变长字符串 存储的，我们选择 文本字符串 存储 ；

    text ：存储字符串（一般存储大文本的路径）
    blob：存储二进制数据（一般不使用，在数据库里面这样的数据，很占用连接时间）

  4. 枚举字符串（enum）

    事先将所有的可能出现的结果都设计好，插入的数据，必须是事先规定的结果中的之一 ；

    定义： enum（可能出现的元素列表）

    优点：

    • 约束数据，插入数据只能是事先固定好的数据之一 ；

    • 节省空间，插入数据，其实插入的字符串对应的数字；

      关于节省空间，需要知道枚举的原理：

      枚举原理：枚举在进行数据定义的时候，系统会自动的建立一个数字与枚举元素的对应关系（关系保存在日志中），然后在进行数据插入的时候，系统会自动将字符转换成对应的数字存储，然后在进行提取的时候，又会自定的转成对应的字符串输出 ；。这也解释了为啥只能存事先规定好的 字符串元素，否则查找关系是找不到对应关系的！但是你可以直接插入对应的数字！

      由于枚举存在一个查关系，进行转换的过程，空间是节省了，但是效率变慢了！

      枚举元素按照事先规定的顺序，从1 开始编号 ；

      知道枚举原理，就很容易知道，为啥内存占用是1或者2，按照顺序从1进行编号，当在前255的时候，使用一个字节，就可以保存了，因为内部保存的是对应的数字，根本不是字符串；大于255，就用2个字节保存 ；

类型名 占用字节 最大存储枚举量 关键点
enum 1、2 65535 内部存储是整数表示

  1. 集合字符串（set）

    集合和枚举很类似，其实内部存储的是数值，而非字符串，但是集合是多选；
    定义： set(元素列表);

    插入元素的时候 ，跟枚举 一样，也可以直接插入数字，但是这里跟 枚举不一样的是 ，数值不是单纯的按照顺序编号的 ；

    在 set 集合中，它对各种可能进行编排，使用二进制位，选中的为1，没选择的为0，然后将其反过来，就是其在数据库中保存的样子；

    因此，当你插入相同的多选，但是顺序不一样的时候 ，存进数据库 是一样，因为它们选中的 状态的二进制位是一样，最后 存进数据库都是一样的

类型名 占用字节 最大集合表示量 关键点
set 1、2、3、4、8 64 内部存储是整数表示，使用二进制位状态

踩坑

在插入时间的时候，报下面的错：

Unknown column '1997-01-22 12:23:22' in 'field list'

这时候，检查你用的是 单引号 ' 还是 反引号 ` ，多半是使用了 反引号！

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/young-youth/p/11665643.html