MySQL常用基础类型详解

MySQL
MySQL
Catalogue
  1. 1. 前言
  2. 2. 整数类型
  3. 3. 实数类型
    1. 3.1. 浮点型(float和double)
    2. 3.2. 定点型(decimal)
  4. 4. 字符串类型
    1. 4.1. varchar
    2. 4.2. char
    3. 4.3. blob和text类型
  5. 5. 日期和时间类型
    1. 5.1. DATETIME的特点
    2. 5.2. TIMESTAMP的特点
  6. 6. 参考资料

前言

以下是对《高性能MySQL》中MySQL基础类型的学习总结

整数类型

有如下几种整数类型，以及它们相应的存储空间:

  • TINYINT，8位存储空间
  • SMALLINT，16位存储空间
  • MEDIUMINT，24位存储空间
  • INT，32位存储空间
  • BIGINT，64位存储空间

以int类型为例，它的存储空间是32位，其可以存储的值的范围是从-2^31到2^31-1。整数类型有可选的unsigned属性，表示不允许负值，对于int来说，其unsigned可以存储范围是0~2^32-1

在MySQL中我们可以为整数类型指定宽度，例如int(11)。实际上这表示能够显示的数值中的数字的个数也就是宽度，它不会限制值的合法范围，int依旧是32位存储空间，这里只是规定了MySQL的一些交互工具(比如Navicat)用来显示字符的个数，对于存储和计算来说，int(1)和int(20)是相同的,因为整型的显示宽度，对数值大小无影响，只是当对类型设置了zerofill的时候，在显示的时候补0而已。

注意：这里的指定宽度不同于varchar类型中指定长度

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数

浮点型(float和double)

浮点型 含义
float(m,d) 单精度浮点型 8位精度(4字节) m表示浮点型数据总共位数，d表示小数位
double(m,d) 双精度浮点型 16位精度(8字节) m表示浮点型数据总共位数，d表示小数位

比如设一个字段定义为float(5,3),插入一个数据12.3334，实际数据库存的是12.333

定点型(decimal)

DECIMAL数据类型用于保留准确精确度的列，语法是 :

1
column_name  DECIMAL(M,D);

上面的语法表示:

  • M是表示有效数字数的精度。 P范围为1〜65。
  • D是表示小数点后的位数。 D的范围是0~30,不得超过M

对于decimal列，可以指定小数点前后所允许的最大位数，这会影响列的空间消耗，比如decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节:小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。

因为需要额外的空间和计算开销，所以应该尽量只对小数进行精确计算时才使用decimal——例如存储财务数据

字符串类型

varchar

varchar 用于存储可变字符串，它比定长类型更节省空间。

varchar 的长度在 MySQL5.0.3 版本之前是0到255字节，在5.0.3和以后的版本中长度是0到65535字节，重要一点是，MySQL5.0.3 和以后的varchar的有效最长长度取决于最大行的大小(65535字节，所有列共享)和所使用的字符集。

平时我们创建表时指定 varchar 的长度实际上是在我们数据库使用的字符集的基础上指定 varchar 能存储多少个字符，不管数字，字母，还是汉字。

举例说明

创建一个长度为5的varchar列

1
2
3
4
mysql> create table test1(
    -> name varchar(5)
    -> );
Query OK, 0 rows affected (0.12 sec)

插入一个长度为6的英文和汉字，都报错提示数据长度太大

1
2
3
4
mysql> insert into test1(name) values("pjmike");
1406 - Data too long for column 'name' at row 1
mysql> insert into test1(name) values("你好呀大世界");
1406 - Data too long for column 'name' at row 1

插入长度小于等于5的字母和汉字，成功

1
2
3
4
mysql> insert into test1(name) values("pj");
Query OK, 1 row affected (0.08 sec)
mysql> insert into test1(name) values("你好呀世界");
Query OK, 1 row affected (0.08 sec)

以上说明 varchar 类型字段的长度实际上表示容纳的最大字符数，而这个最大字符数是 「行定义长度不超过65535字节」和「数据库的字符集」 这两者通过公式运算得来的

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> create table test2(
    -> name varchar(65535)
    -> );
1074 - Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4     
+--------------------------+----------------------------+
8 rows in set (0.52 sec)

由上面可以看出，该数据库的字符集为 「uft8mb4」对应的 varchar 最大字符数为16383

varchar 还有一点需要注意的是: varchar需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节，则使用1个字节表示，否则使用2个字节。

char

char类型是定长的: MySQL 总是根据定义的字符串长度分配足够的空间。当定义 char 值时，MySQL 会删除所有的末尾空格，适合存储短字符。

blob和text类型

blob 和 text 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

实际上，它们分别属于两组不同的数据类型家族: 字符类型是 TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT；对应的二进制类型是 TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。BLOB 是 SMALLBLOB的同义词，TEXT 是 SMALLTEXT 的同义词。

日期和时间类型

表示时间值的日期和时间类型有DATETIME，DATE，TIMESTAMP，TIME和YEAR
time

DATETIME的特点

  • 能保存大范围的值，精度为秒
  • 与时区无关，使用8个字节的存储空间
  • 默认情况下，MySQL以一种可排序的，无歧义的格式显示 DATETIME值

TIMESTAMP的特点

  • TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数，它和Unix时间戳相同
  • TIMESTAMP只使用4个字节存储空间，它的范围比DATETIME小的多，只能表示1970-2038年
  • TIMESTAMP依赖于时区
  • 默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL设置这个列的值为当前时间，并且有专有的自动更新特性
  • 默认为NOT NULL
  • 尽量使用TIMESTAMP,它比DATETIME空间效率更高

参考资料

Bagikan Komentar