MySQL从零开始:05 MySQL数据类型

距离上次更新 MySQL 从零开始系列,已经过去了十几天,时间隔得有点长,由于我选用的是 MySQL 的最新版本,网上的教程大多停留在 MySQL 5.x,所以要参阅一下官方文档,而文档是英文的,看起来比较耗时,希望大家可以理解,此系列一定会更新到底

前面的讲解中已经接触到了表的创建,表的创建是对字段的声明,比如:

mysql> create table class(
    -> id int primary key auto_increment,
    -> name varchar(10) not null default '',
    -> gender char(1) not null default '',
    -> company varchar(20) not null default '',
    -> salary decimal(6,2) not null default 0.00
    -> )engine myisam charset utf8;

上述语句声明了字段的名称、类型、所占空间、默认值 和是否可以为空等信息。其中的 intvarcharchardecimal 都是 MySQL 所支持的数据类型,本节将详细介绍 MySQL 支持的数据类型。

MySQL 中定义数据字段的类型对数据库的优化非常重要。MySQL中的每种数据类型都可以通过以下特征来确定:

  • 它用来表示数据值。
  • 占用的空间以及值是固定长度还是可变长度。
  • 数据类型的值可以被索引。
  • MySQL如何比较特定数据类型的值。

MySQL 支持的数据类型可以分为 数值日期/时间字符串(字符)类型,另外还有空间数据类型JSON数据类型,本节以前三种数据类型为主,最后两种简单介绍一下。

1

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

1.1 整形数值

以 TINYINT 为例详细介绍整形数值型的一些属性及操作。

TINYINT[(M)][UNSIGNED][ZEROFILL]

M表示每个值的位数,此处为显示的位数,并不是占用字节大小。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。当结合可选扩展属性ZEROFILL使用时, 默认补充的空格用零代替。例如,对于声明为INT(5) ZEROFILL的列,值5检索为00005。

注意:M 要与 ZEROFILL 配合使用才有效果。

如果一个数值列指定为 ZEROFILL, MySQL自动为该列添加 UNSIGNED 属性。

mysql> create database datatype;
Query OK, 1 row affected (0.03 sec)

mysql> use datatype;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table int_type(
    -> id TINYINT not null default 0,
    -> age TINYINT(4) not null default 0,
    -> distance TINYINT(4) ZEROFILL not null default 0,
    -> score TINYINT UNSIGNED not null default 0,
    -> ranking TINYINT ZEROFILL not null default 0
    -> )engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> desc int_type;
+----------+------------------------------+------+-----+---------+-------+
| Field    | Type                         | Null | Key | Default | Extra |
+----------+------------------------------+------+-----+---------+-------+
| id       | tinyint(4)                   | NO   |     | 0       |       |
| age      | tinyint(4)                   | NO   |     | 0       |       |
| distance | tinyint(4) unsigned zerofill | NO   |     | 0000    |       |
| score    | tinyint(3) unsigned          | NO   |     | 0       |       |
| ranking  | tinyint(3) unsigned zerofill | NO   |     | 000     |       |
+----------+------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

1.2 浮点型数值

对于浮点列类型,在MySQL中单精度值使用4个字节,双精度值使用8个字节。

DOUBLE[(M,D)][UNSIGNED][ZEROFILL]

  • M:精度,代表“总位数”;
  • D:标度,代表小数位(小数点右边的位数)

1.3 布尔值

MySQL 没有内置的 BOOLEANBOOL 数据类型,使用最小的整数类型,也就是 TINYINT(1) 来表示。

2

日期和时间类型

表示时间值的日期和时间类型为 DATETIMEDATETIMESTAMPTIMEYEAR

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP 类型有专有的自动更新特性,将在后面描述。

注意:

  • 包含两位数年份值的日期是不明确的,因为世纪是未知的,MySQL 使用如下规则解释两位数的年份值: 1. 年份值在70~99之间转换为1970~1999 2. 年份值在00~69之间转换为2000~2069
  • 通过连接器/ODBC使用的“零”值日期或时间值将自动转换为NULL,因为ODBC无法处理这些值。

3

字符串类型

在MySQL中,字符串可以容纳从纯文本到二进制数据(如图像或文件)的任何内容。 字符串类型指 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

3.1 CHAR 和 VARCHAR

CHAR 和 VARCHAR 类型类似,但它们保存检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

CHAR和VARCHAR类型声明时需要一个长度值,该值表示你想要想要存储的字符的最大数量。例如,CHAR(30)可以容纳30个字符。 CHAR 列的宽度在创建表时已经固定下来了,如果插入记录该字段的宽度不足指定宽度,那么要在右侧自动填补 空格。检索CHAR值时,除非启用 PAD_CHAR_TO_FULL_LENGTH SQL模式,否则将删除尾部空格。

存储 VARCHAR 值时,不会填充。在与标准SQL一致的情况下,存储和检索值时保留尾部空格。

下表展示了CHAR和VARCHAR之间的差异,它展示了将各种字符串值存储到CHAR(4)和VARCHAR(4)列的结果(假设此列使用了一个单字节字符集,如latin1)。

请看下面例子:

01.vchar_varchar

3.2 BINARY 和 VARBINARY

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值。

3.3 BLOB 和 TEXT

BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。四种 TEXT 类型是TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。它们对应于四个 BLOB 类型,并且具有相同的最大长度和存储要求。

3.4 ENUM 类型

ENUM 是一个字符串对象,它从一个允许值列表中选择了一个值,这些值在表创建时显式地列出了列规范中。它有这些优势:

  • 在一列有有限的可能值集合的情况下,压缩数据存储。你指定为输入值的字符串被自动编码为数字。
  • 可读的查询和输出。在查询结果中,这些数字被转换回相应的字符串。

枚举值必须是引用的字符串文字。 你可以这样用枚举列创建表:

mysql> create table shirts (
    -> name varchar(40),
    -> size enum('x-small', 'small', 'medium', 'large')
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into shirts 
    -> (name, size)
    -> values 
    -> ('dress shirt', 'large'),
    -> ('t-shirt', 'medium'),
    -> ('polo shirt', 'small');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select name, size from shirts where size = 'large';
+-------------+-------+
| name        | size  |
+-------------+-------+
| dress shirt | large |
+-------------+-------+
1 row in set (0.00 sec)

假如向该表中插入一百万条 值为 'medium' 的记录,存储空间需要一百万字节。相比而言,如果以字符串 'medium' 存储,则需要六百万字节。

3.5 SET 类型

SET 是一个字符串对象,可以有0个或多个值,并且每一个值都必须从表创建时指定的允许值列表中选择。SET 的列值由多个集合成员组成的,用逗号(,)分隔。这样的话,SET成员值本身不应该包含逗号。

例如,由 SET('one', 'two') NOT NULL 指定的数据列可以有这些值:

''
'one'
'two'
'one,two'

SET 列最多可以包含64个不同的成员。

看如下例子:

mysql> INSERT INTO myset (col) VALUES 
    -> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select col from myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.00 sec)

从查询结果可以看出:如果一条记录里已经包含 SET 的一个成员了,就不会重复保存该值。

4

空间数据类型

MySQL支持许多包含各种几何和地理值的空间数据类型,如下表所示:

5

JSON 数据类型

MySQL 支持由 RFC 7159 规定的原生 JSON 数据类型,以更有效地存储和管理JSON文档。 本机JSON数据类型提供JSON文档的自动验证和最佳存储格式。 JSON 列不能有默认值

mysql> CREATE TABLE myjson (jdoc JSON);
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO myjson 
    -> VALUES 
    -> ('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT jdoc FROM myjson;
+--------------------------------------+
| jdoc                                 |
+--------------------------------------+
| {"key1": "value1", "key2": "value2"} |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO myjson 
    -> VALUES 
    -> ('[1, 2');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or ']' after an array element." at position 5 in value for column 'myjson.jdoc'.

可以看到,如果我们插入的记录 JSON 数据格式不正确是不能成功的。

原文发布于微信公众号 - C与Python实战(CPythonPractice)

原文发表时间:2018-08-02

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏GreenLeaves

SQL练习之求解填字游戏

SELECT * FROM dbo.spt_values ? 假设我们需要找具有以下特点的单词: (1)第二个字母:a (2)第四个字母:l (3)一共只有5个...

204100
来自专栏祥子的故事

sql | 基础总结 | 思维导图

44960
来自专栏互联网开发者交流社区

用于 SELECT 和 WHERE 子句的函数

16930
来自专栏Java后端生活

MySQL(三)基础查询

15690
来自专栏乐沙弥的世界

PL/SQL 联合数组与嵌套表

      通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。单列多行数据 则由联合数组...

14030
来自专栏栗霖积跬步之旅

第六章:过滤数据

表名:products 字段:product_id、product_name、product_price、vend_id(供应商) 1.使用WHERE子句:...

19660
来自专栏Ken的杂谈

SQL开发中的良好习惯

13710
来自专栏撸码那些事

MySQL——优化ORDER BY语句

本篇文章我们将了解ORDER BY语句的优化,在此之前,你需要对索引有基本的了解,不了解的老少爷们可以先看一下我之前写过的索引相关文章。现在让我们开始吧。

16020
来自专栏Python

表的数据类型

一 介绍 存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的 详细参考: http://www.runoob....

22270
来自专栏吾爱乐享

软件测试之学习mysql的查询功能select及高级查询(重中之重)

14220

扫码关注云+社区

领取腾讯云代金券