MySQL讲义第3讲——MySQL 的数据类型

程序员文章站 2022-05-28 16:12:23

...

MySQL讲义第3讲——MySQL 的数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串类型。

一、数值类型

数值类型分为整型、定点数类型、浮点数类型。

1、整型

整型包括 TINYINT、SMALLINT、MEDIUMINT、 INT、 BIGINT 等类型。具体用法如下表所示：

MySQL数据类型	含义
tinyint(m)	1个字节范围：-128~127
smallint(m)	2个字节范围：-32768~32767
mediumint(m)	3个字节范围：-8388608~8388607
int(m)	4个字节范围：-2147483648~2147483647
bigint(m)	8个字节范围：-9.22×10^{18—9.22×10}18

说明：
1、可以使用 unsigned 修饰符定义无符号整型，如 tinyint unsigned 的取值范围为(0~256)。
2、int(m)中的 m 表示查询结果集中的显示宽度，并不影响实际的取值范围。

2、浮点数类型

浮点数类型包括 float 和 double 两类，具体使用方法如下表所示：

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

说明：
把某个字段定义为 float(6,3)，表示该字段保留3位小数，总长度为6位，即整数最大为3位。
（1）如果插入一个数 123.45678，实际数据库里存的是 123.457；
（2）如果插入一个数 12.123456，存储的是 12.1234，如果插入 12.12，存储的是 12.1200。

举例：

使用 create table like 命令可以利用一个已经存在的表创建一个新表。语法如下：

mysql> create table t1(id int primary key,name char(20),height float(6,3));
Query OK, 0 rows affected (0.03 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t1(id int primary key,name char(20),weight float(6,3));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1,'apple',1.5);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(2,'orange',10.502458);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(3,'banana',120.3758);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(4,'pear',1200.3758);
ERROR 1264 (22003): Out of range value for column 'weight' at row 1

mysql> select * from t1;
+----+--------+---------+
| id | name   | weight  |
+----+--------+---------+
|  1 | apple  |   1.500 |
|  2 | orange |  10.502 |
|  3 | banana | 120.376 |
+----+--------+---------+
3 rows in set (0.00 sec)

3、定点数类型

浮点数类型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。浮点数类型只有一种，即：decimal(m,d)，m 和 d 的含义与浮点数类型完全相同。其中参数 m<65，d<30。

举例：

mysql> create table t2(id int primary key,name char(20),height decimal(6,3));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values(1,'apple',1.5);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(2,'orange',10.502458);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> insert into t2 values(3,'banana',120.3758);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> insert into t2 values(4,'pear',1200.3758);
ERROR 1264 (22003): Out of range value for column 'height' at row 1
mysql> select * from t2;
+----+--------+---------+
| id | name   | height  |
+----+--------+---------+
|  1 | apple  |   1.500 |
|  2 | orange |  10.502 |
|  3 | banana | 120.376 |
+----+--------+---------+
3 rows in set (0.00 sec)

二、字符串类型

字符串类型包括：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 、SET 等类型。

1、CHAR 和 VARCHAR类型

说明：
1、 语法格式为： CHAR(n)， VARCHAR(n)
2、 CHAR 和 VARCHAR 类型需要指定长度，表示要保存的最大字符数。例如： CHAR(30)表示可以占用30个字符。
3、 CHAR 列的长度可以为0到255之间的任何值。当保存 CHAR 类型的值时，如果字符数少于指定的长度，则在右边填充空格以达到指定的长度。当检索 CHAR 类型的值时，尾部的空格被删除。所以，我们在存储 char 类型的值时，字符串结尾不能有空格，即使有，查询出来后也会被删除。
4、 VARCHAR 列中的值为可变长字符串，长度可以指定为0到65535之间的值。与 CHAR 类型相比， VARCHAR 类型的值保存时只保存需要的字符数，另加一个字节来记录长度（如果列声明的长度超过255，则使用两个字节）。 VARCHAR 类型的值如果需要存储的字符个数少于指定的长度，不在尾部填充空格。检索时尾部的空格仍保留。
5、 如果需要保存的字符串的长度超过了 CHAR 或 VARCHAR 列指定的长度，会出现错误。
6、 char 类型的字符串检索速度要比 varchar 类型的快。

举例：

mysql> create table if not exists employee(
         id int primary key,
         name char(10),
         addr varchar(100)
     );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into employee values(1001,'Jack','Beijing');
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee values(1002,'Mark  ','Shanghai  ');
Query OK, 1 row affected (0.03 sec)

--输入的姓名超出 char 类型定义的长度，出现错误。
mysql> insert into employee values(1003,'Thomas Edison','Washington');
ERROR 1406 (22001): Data too long for column 'name' at row 1

--查询时 char 类型结尾的空格被删除， varchar 类型结尾的空格保留
mysql> select id,name,concat(addr,'11') from employee;
+------+------+-------------------+
| id   | name | concat(addr,'11') |
+------+------+-------------------+
| 1001 | Jack | Beijing11         |
| 1002 | Mark | Shanghai  11      |
+------+------+-------------------+
2 rows in set (0.00 sec)

--字段字符串长度时， char 类型忽略尾部空格， varchar 类型尾部空格也被计算。
mysql> select id,name,length(name),addr,length(addr) from employee;
+------+------+--------------+------------+--------------+
| id   | name | length(name) | addr       | length(addr) |
+------+------+--------------+------------+--------------+
| 1001 | Jack |            4 | Beijing    |            7 |
| 1002 | Mark |            4 | Shanghai   |           10 |
+------+------+--------------+------------+--------------+
2 rows in set (0.00 sec)

2、BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR 类型。不同的是，它们存储的是二进制串，因此没有字符集。

当保存 BINARY 数据值时，在尾部填充 0x00（零）值以达到指定长度。取值时不删除尾部的字节。对于 VARBINARY，插入时不填充字符，选择时不裁剪字节。

举例：

mysql> create table t3(id int,name binary(10),addr varbinary(100));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t3;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id    | int(11)        | YES  |     | NULL    |       |
| name  | binary(10)     | YES  |     | NULL    |       |
| addr  | varbinary(100) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL,
  `name` binary(10) DEFAULT NULL,
  `addr` varbinary(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> insert into t3 values(1,'Jack','Beijing');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t3;
+------+------------+---------+
| id   | name       | addr    |
+------+------------+---------+
|    1 | Jack       | Beijing |
+------+------------+---------+
1 row in set (0.00 sec)

3、BLOB 和 TEXT 类型

（1）BLOB 是一个二进制大对象，可以容纳可变数量的数据。有4种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和LONGBLOB。这四种类型的用法完全相同，区别仅仅在于可容纳值的最大长度不同。

（2）TEXT 类型有4种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

（3）BLOB 列可以被看作二进制字符串，TEXT 列可以被看作字符字符串，类似 CHAR 和 BINARY。可以将 BLOB 列看作足够大的 VARBINARY 列。可以将 TEXT 列看作足够大的 VARCHAR列。

4、ENUM（枚举）和SET（集合）类型

（1）ENUM（枚举）类型

说明：
（1）MySQL 中的 ENUM 类型是一个字符串对象，其值来自创建表时在列规定中显式枚举的值。
（2）插入数据时只能从枚举列表中选择一个选项插入，不能同时插入多个选项，这是和 SET 类型的最大区别。
（3）可以插入NULL。
（4）ENUM（枚举）类型的索引规则如下：对各个枚举值从1开始依次编号， ENUM 值根据索引编号进行排序。
例如：对于ENUM('a'，'b')，'a'排在'b'前面，但对于ENUM('b'，'a')，'b'排在'a'前面。

举例：

定义一个包含枚举类型的表：

mysql> create table if not exists stu3(
         id int primary key,
         name char(20),
         gender enum('男','女'),
         Political_landscape enum('1','2','3')
     );
Query OK, 0 rows affected (0.02 sec)

mysql> delete from stu3;
Query OK, 3 rows affected (0.02 sec)

mysql> insert into stu3 values(1001,'Zhao','女','2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu3 values(1002,'Wang',null,'2');
Query OK, 1 row affected (0.04 sec)

mysql>  insert into stu3 values(1003,'Wang','男','2');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu3;
+------+------+--------+---------------------+
| id   | name | gender | Political_landscape |
+------+------+--------+---------------------+
| 1001 | Zhao | 女     | 2                   |
| 1002 | Wang | NULL   | 2                   |
| 1003 | Wang | 男     | 2                   |
+------+------+--------+---------------------+
3 rows in set (0.00 sec)

对查询结果进行排序：

mysql> select * from stu3 order by gender;
+------+------+--------+---------------------+
| id   | name | gender | Political_landscape |
+------+------+--------+---------------------+
| 1002 | Wang | NULL   | 2                   |
| 1003 | Wang | 男     | 2                   |
| 1001 | Zhao | 女     | 2                   |
+------+------+--------+---------------------+
3 rows in set (0.00 sec)

（2）SET（集合）类型

说明：
（1）SET 类型定义时的语法和 ENUM 类型完全相同。使用时的唯一区别在于插入数据时 ENUM 类型只能选择一个选项插入，而 SET 类型可以插入多个选项。
（2）SET 是一个字符串对象，可以有零个或多个值，其值来自创建表时在列规定中设置的选项。
（3）SET 类型最多可以设置64个值。
（4）对于包含多个 SET 元素的值，当插入值时元素所列的顺序并不重要。

举例：创建一个包含 SET 类型的表

mysql> create table if not exists user_permission(
         id int UNSIGNED not null auto_increment primary key,
         user_id int not null,
         permission set('阅读','评论','发帖') not null,
         unique (user_id)
     );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user_permission(user_id,permission) 
    -> values (1,'阅读'),(2,'阅读'),(3,'阅读,评论'),(4,'阅读,评论,发帖');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from user_permission;
+----+---------+----------------------+
| id | user_id | permission           |
+----+---------+----------------------+
|  1 |       1 | 阅读                 |
|  2 |       2 | 阅读                 |
|  3 |       3 | 阅读,评论            |
|  4 |       4 | 阅读,评论,发帖       |
+----+---------+----------------------+
4 rows in set (0.00 sec)

举例：对 SET 类型的字段进行查询操作

mysql> select * from user_permission where permission = '阅读';
+----+---------+------------+
| id | user_id | permission |
+----+---------+------------+
|  1 |       1 | 阅读       |
|  2 |       2 | 阅读       |
+----+---------+------------+
2 rows in set (0.00 sec)

mysql> select * from user_permission where permission = '阅读,评论';
+----+---------+---------------+
| id | user_id | permission    |
+----+---------+---------------+
|  3 |       3 | 阅读,评论     |
+----+---------+---------------+
1 row in set (0.00 sec)

--集合中第一个选项的编号为1，第二个为2，第三个为4，第四个为8，以此类推。因此：
--查询包含第一项的条件为：permission = 1
--查询包含第一项和第二项的条件为：permission = 3（1+2）
--查询包含第二项和第三项的条件为：permission = 6（2+4）
--查询包含第一项和第三项的条件为：permission = 5（1+4）
--查询包含前三项的条件为：permission = 7（1+2+4）
mysql> select * from user_permission where permission = 1;
+----+---------+------------+
| id | user_id | permission |
+----+---------+------------+
|  1 |       1 | 阅读       |
|  2 |       2 | 阅读       |
+----+---------+------------+
2 rows in set (0.00 sec)

mysql> select * from user_permission where permission = 3;
+----+---------+---------------+
| id | user_id | permission    |
+----+---------+---------------+
|  3 |       3 | 阅读,评论     |
+----+---------+---------------+
1 row in set (0.00 sec)

mysql> select * from user_permission where permission = 6;
Empty set (0.00 sec)

mysql> select * from user_permission where permission = 5;
Empty set (0.00 sec)

mysql> select * from user_permission where permission = 7;
+----+---------+----------------------+
| id | user_id | permission           |
+----+---------+----------------------+
|  4 |       4 | 阅读,评论,发帖       |
+----+---------+----------------------+
1 row in set (0.00 sec)

三、日期时间类型

日期时间类型包括：Date、DateTime、TimeStamp、Time、Year

MySQL数据类型	含义
date	日期： ‘2008-12-2’，只需要日期值而不需要时间部分时应使用此类型。
time	时间： ‘12:25:36’
datetime	日期时间： ‘2008-12-2 22:06:44’，需要同时包含日期和时间信息的值时使用此类型。
timestamp	时间戳：自动存储记录修改时间

举例：创建一个包含 datetime 类型的数据表

mysql> create table if not exists t4(
         id int primary key,
         name char(20),
         birth datetime
     );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t4 values(10001,'Mike','1998-12-23');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(10002,'Jack','1999-2-15 18:25:23');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t4;
+-------+------+---------------------+
| id    | name | birth               |
+-------+------+---------------------+
| 10001 | Mike | 1998-12-23 00:00:00 |
| 10002 | Jack | 1999-02-15 18:25:23 |
+-------+------+---------------------+
2 rows in set (0.00 sec)

四、数据类型选择的基本原则

基本原则：在满足使用需求的前提下，采用从小原则（即尽量采用占用存储空间小的数据类型），比如能用 TINYINT 时最好不用 INT，能用 FLOAT 类型的就不用DOUBLE 类型，以节省存储空间并提高运行效率，尤其是当数据量非常大的情况下。

另外：要注意各种数据类型的特点以及使用限制。还要考虑各种存储引擎对数据类型的影响。

相关标签： MySQL讲义 MySQL基本操作数据库 mysql

上一篇：《SLAM十四讲》中Sophus库的编译问题

下一篇： ipynb文件转py文件其他语言文件

MySQL讲义第3讲——MySQL 的数据类型

MySQL讲义第3讲——MySQL 的数据类型

一、数值类型

1、整型

2、浮点数类型

3、定点数类型

二、字符串类型

1、CHAR 和 VARCHAR类型

2、BINARY 和 VARBINARY 类型

3、BLOB 和 TEXT 类型

4、ENUM（枚举）和SET（集合）类型

（1）ENUM（枚举）类型

（2）SET（集合）类型

三、日期时间类型

四、数据类型选择的基本原则

MYSQL批量插入数据的实现代码第1/3页

MYSQL批量插入数据的实现代码第1/3页

脚本安全的本质_PHP+MYSQL第1/3页

marc by marc jacobs 官网脚本安全的本质_PHP+MYSQL第1/3页

MySQL优化之表结构优化的5大建议（数据类型选择讲的很好）_MySQL