MySQL学习笔记（2）—— 数据类型和列类型
MySQL支持大量的列类型，它可以被分为3类：数字类型、日期和时间类型以及字符串(字符)类型。首先给出可用类型的一个概述，并且总结每个列类型的存储需求，然后提供每个类中的类型性质的更详细的描述。
MySQL的基本数据类型
数字
MySQL中的数字是类似于100或3.1415926这样的值。MySQL支持说明为整数(无小数部分)或浮点数(有小数部分)的值:
- 整数由数字序列组成；浮点数由一个阿拉伯数字序列、一个小数点和另一个阿拉伯数字序列组成。两个阿拉伯数字序列可以分别为空，但不能同时为空。
- MySQL支持科学表示法，科学表示法由整数或浮点数后跟“e”或“E”、一个符号(“+”或“-”，必须具有)和一个整数指数来表示。 数值前可放一个负号“-”以表示负值。
十六进制数
MySQL支持十六进制值。以十六进制形式表示的整数由“0x”后跟一个或多个十六进制数字(”0”到“9”及“a”到“f”)组成。十六进制数字不区分大小写，但其前缀“0x”不能为“0X”。
日期和时间值
日期和时间值是一些类似于“1999-06-17”或“12:30:43”这样的值。MySQL还支持日期/时间的组合,如“1999-06-17 12:30:43”。需要注意的是，MySQL是按年-月-日的顺序表示日期的，这可能与你学过的高 级语言不同。
字符串
字符串既可以是用双引号括起来，也可以是用单引号。例如：“I like MySQL”或'MySQL is powerful'。
表格 转意序列列表
| 符号 | 含义 | 符号 | 含义 |
|---|---|---|---|
| \0 | ASCII 0(NUL)字符 | \n | 换行 |
| \r | 回车 | \t | 定位符 |
| \b | 退格 | \ | 反斜杠 |
| ' | 单引号 | " | 双引号 |
| % | 百分号 | _ | 下划线 |
NULL值
NULL值可适用于各种列类型，它通常用来表示“没有值”、 “无数据”等意义，并且不同于例如数字类型的0为或字符串类型的空字符串。
注意 NUL字节与NULL值不同：NUL为一个零值字节，而 NULL 代表没有值。
MySQL的列类型
数据库中的每个表都是由一个或多个列构成的。可以用 CREATE TABLE 语句创建一个表，创建表时要为每列指定一个类型。列的类型与数据类型相对应，但是比数据类型更为具体,用列类型描述表列可能包含的值的种类以及范围，列的值必须符合规定，不能包含对应的数据类型所允许的所有值。例如，CHAR(16)就规定了存储的字符串值必须是16位。当然不是你必须存储16个字符，而是指列在表中要占16个字符的宽度的。
MySQL 的列类型是一种手段，通过这种手段可以描述一个表列包含什么类型的值，这又决定了 MySQL 怎样处理这些值。例如，数据值既可用数值也可用串的列类型来存放，但是根据存放这些值的类型，MySQL 对它们的处理将会有些不同。每种列类型都有几个特性如下:
- 其中可以存放什么类型的值
- 值要占据多少空间，以及该值是否是定长的或可变长的
- 该类型的值怎样比较和存储
- 该类型是否允许 NULL 值
- 该类型是否可以索引
下面是创建一个表的例子：
CREATE TABLE Teacher
(
ID TINYINT UNSIGNED NOT NULL,
Name CHAR(16) NOT NULL,
Tel NUMERIC(8),
Sex ENUM("F","M") DEFAULT "M"
)
由上面这个例子可以知道，创建列类型的语法是:
<pre><code>
col_name col_type [col_attributes][general_attributes]
</code></pre>
其中：
- col_name 列的名字
- col_type 列类型，控制存储在列中的数据类型
- col_attributes 专用属性，只能应用于制定列
- general_attributes 通用属性，例如上面提到了NULL、NOT NULL和DEFAULT
为方便使用，下面以文字说明和表格的形式展示MySQL的常见列类型。
数字列类型
MySQL支持所有的ANSI/ISO SQL92的数字类型。这些类型包括准确数字的数据类型 (NUMERIC, DECIMAL, INTEGER和 SMALLINT)，也包括近似数字的数据类型(FLOAT, REAL,和 DOUBLE PRECISION)。关键词 INT 是 INTEGER 的一个同义词,而关键词 DEC 是 DECIMAL 一个同义词。
MySQL 的数字列类型有两种:
- 整型 MySQL 提供了五种整型 TINYINT、SAMLLINT、INT、 MEDIUMINT 和 BIGINT。整数列可以用 UNSIGNED 禁用负数值。
- 浮点型 MySQL提供了三种浮点型，FLOAT、DOUBLE和DECIMAL。
表格 MySQL的数字列类型——整数类型
| 类型名 | 取值范围(有符号或无符号) | 占用存储(字节) |
|---|---|---|
| TINYINT | -128～127或0～2^8-1) | 1 |
| SMALLINT | -32768～32767或0～2^16-1 | 2 |
| MEDIUMINT | -223～223-1或0～2^24-1 | 3 |
| INT | -231～231-1或0～2^32-1 | 4 |
| BIGINT | -263～263-1或0～2^64-1 | 8 |
例如，建立一个数字列表格，输入以下代码：
CREATE TABLE number
(
tiny TINYINT,u_tiny TINYINT UNSIGNED,
small SMALLINT,u_small SMALLINT UNSIGNED,
medium MEDIUMINT,u_medium MEDIUMINT UNSIGNED,
num INT,u_num INT UNSIGNED,
big BIGINT,u_big BIGINT UNSIGNED
)
然后显示这个表的结构
<pre><code>
DESCRIBE number
</pre></code>
得到表格输出结果如下：
+----------+-----------------------+
| Field | Type |
+----------+-----------------------+
| tiny | tinyint(4) |
| u_tiny | tinyint(3) unsigned |
| small | smallint(6) |
| u_small | smallint(5) unsigned |
| medium | mediumint(9) |
| u_medium | mediumint(8) unsigned |
| num | int(11) |
| u_num | int(11) unsigned |
| big | bigint(20) |
| u_big | bigint(20) unsigned |
+----------+-----------------------+
表格 MySQL的数字列类型——浮点类型
| 类型名 | 占用存储(字节) |
|---|---|
| FLOAT | 4 |
| DOUBLE | 8 |
| DECIMAL(M) | M+2 (ver >3.23) |
在为列选择了使用某种数值类型时，除了要考虑数据的类型外，还应该注意所要表示的值的范围和存储需求，只需选择能覆盖要取值的范围的最小类型即可。
日期和时间列类型
表格 MySQL的日期和时间列类型
| 类型名 | 含义 | 类型名 | 含义 |
|---|---|---|---|
| DATE | 日期, 'YYYY-MM-DD' | TIME | 时间, 'HH:MM:SS' |
| DATETIME | 时间和时间组合, 'YYYY-MM-DD HH:MM:SS' | TIMESTAMP | 时间戳,以 YYYMMDDHHMMSS 格式来显示 |
举例，生成一个日期和时间表，并插入2个元素：
CREATE TABLE my_test
(
id INT,
ts TIMESTAMP
)
INSERT my_test VALUES(1,20010101000000)
INSERT my_test(id) VALUES(2)
SELECT * from my_test
显示得到结果输出如下：
+------+----------------+
| id | ts |
+------+----------------+
| 1 | 20010101000000 |
| 2 | 20010113165713 |
+------+----------------+
字符串列类型
MySQL 提供的字符串类型包括 CHAR、VARCHAR、BLOB、TEXT、ENUM 和 SET。 对这些类型作一个简要的叙述如下:
表格 MySQL的字符串列类型
| 类型名 | 含义 | 类型名 | 含义 |
|---|---|---|---|
| CHAR | 定长字符串 | VARCHAR | 变长字符串 |
| TINYBLOB | BLOB(2^8-1) | TINYTEXT | TEXT(2^8-1) |
| BLOB | BLOB(2^16-1) | TEXT | TEXT(2^16-1) |
| MEDIUMBLOB | BLOB(2^24-1) | MEDIUMTEXT | TEXT(2^24-1) |
| LONGBLOB | BLOB(2^32-1) | LONGTEXT | TEXT(2^32-1) |
| ENUM('value1','value2',...) | 枚举:列只能赋值为某个枚举成员或NULL | SET('value1','value2',...) | 集合:列可以赋值为多个集合成员或NULL |
表格中，BLOB是一个能保存可变数量的数据的二进制的大对象。在某种意义上，串实际是一种非常“通用”类型，因为可用它们来表示任意值，不仅仅是字符串。例如，可用串类型来存储二进制数据，如图像、视频或音频。
小结
本节对 MySQL 的数据类型和列类型进行了简单的描述，是我们继续学习 SQL 语言的基础。文中出现大量的 SQL 语句，尤其是建表的语句，你可能现在还不知道如何完成这些查询，你只需要理解其中的含义就可以了，因为阅读了后面的章节你就会理解这 一切。
