5.1 MySQL数据类型介绍
MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。
⑴ 数值数据类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE、定点小数类型DECIMAL、
⑵ 日期/时间类型：包括YEAR、TIME、DATE、DATETIME、和TIMESTAMP
⑶ 字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。
5.1.1 整数类型
数值型数据类型主要用来存储数字，MySQL提供多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，其所需要的存储空间也会越大。MySQL主要提供的整数类型有：TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER）、BIGINT。整型类型属性字段可以添加AUTO_INCREMENT自增约束条件。【表5.1】列出了MySQL中的数值类型。
表5.1 MySQL中的整数型数据类型
|
类型名称
|
说明
|
存储需求
|
|
TINYINT
|
很小的整数
|
1个字节
|
|
SMALLINT
|
小的整数
|
2个字节
|
|
MEDIUMINT
|
中等大小的整数
|
3个字节
|
|
INT(INTEGER）
|
普通大小的整数
|
4个字节
|
|
BIGINT
|
大整数
|
8个字节
|
从表中可以看到，不同类型整数存储所需的字节数是不同的，占用字节数最小的是TINYINT类型，占用字节最大的是BITINT类型，相应的占用字节越多的类型所能表示的数值范围越大。根据占用字节数可以求出每一种数据类型的取值范围，例如TINYINT需要1个字节（8 bits）来存储，那么TINYINT无符号数的最大值为28-1，即255，TINYINT有符号数的最大值为27-1，即127。其他类型的整数的取值范围计算方法相同，其取值范围如【表5.2】。
表5.2 不同整数类型的取值范围
<tbody>
<tr class="firstRow">
<td style="height:18.55pt; vertical-align:top; width:92.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong><span style="font-family:宋体">数据类型</span></strong></span></span></p>
</td>
<td style="height:18.55pt; vertical-align:top; width:219.15pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong><span style="font-family:宋体">有符号</span></strong></span></span></p>
</td>
<td style="height:18.55pt; vertical-align:top; width:135.0pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"Times New Roman""><strong><span style="font-family:宋体">无符号</span></strong></span></span></p>
</td>
</tr>
<tr>
<td style="height:17.65pt; vertical-align:top; width:92.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">TINYINT</span></span></p>
</td>
<td style="height:17.65pt; vertical-align:top; width:219.15pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">-128<span style="font-family:宋体">到</span>127</span></span></p>
</td>
<td style="height:17.65pt; vertical-align:top; width:135.0pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">0<span style="font-family:宋体">到</span>255</span></span></p>
</td>
</tr>
<tr>
<td style="height:18.55pt; vertical-align:top; width:92.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">SMALLINT</span></span></p>
</td>
<td style="height:18.55pt; vertical-align:top; width:219.15pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">32768<span style="font-family:宋体">到</span>32767</span></span></p>
</td>
<td style="height:18.55pt; vertical-align:top; width:135.0pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">0<span style="font-family:宋体">到</span>65535</span></span></p>
</td>
</tr>
<tr>
<td style="height:17.65pt; vertical-align:top; width:92.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">MEDIUMINT</span></span></p>
</td>
<td style="height:17.65pt; vertical-align:top; width:219.15pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">-8388608<span style="font-family:宋体">到</span>8388607</span></span></p>
</td>
<td style="height:17.65pt; vertical-align:top; width:135.0pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">0<span style="font-family:宋体">到</span>16777215</span></span></p>
</td>
</tr>
<tr>
<td style="height:14.65pt; vertical-align:top; width:92.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">INT(INTEGER)</span></span></p>
</td>
<td style="height:14.65pt; vertical-align:top; width:219.15pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">-2147483648<span style="font-family:宋体">到</span>2147483647</span></span></p>
</td>
<td style="height:14.65pt; vertical-align:top; width:135.0pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">0<span style="font-family:宋体">到</span>4294967295</span></span></p>
</td>
</tr>
<tr>
<td style="height:15.65pt; vertical-align:top; width:92.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">BIGINT</span></span></p>
</td>
<td style="height:15.65pt; vertical-align:top; width:219.15pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">-9223372036854775808<span style="font-family:宋体">到</span>9223372036854775807</span></span></p>
</td>
<td style="height:15.65pt; vertical-align:top; width:135.0pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"Times New Roman"">0<span style="font-family:宋体">到</span>18446744073709551615</span></span></p>
</td>
</tr>
</tbody></table><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">在第四章开始创建表章节中，有如下创建表的语句：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">CREATE TABLE tb_emp1</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">(</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">id INT(11),</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">name VARCHAR(25),</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">deptId INT(11),</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">salary FLOAT</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">);</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:"Times New Roman"">id</span><span style="font-family:宋体">字段的数据类型为</span><span style="font-family:"Times New Roman"">INT(11)</span><span style="font-family:宋体">，注意到后面的数字</span><span style="font-family:"Times New Roman"">11</span><span style="font-family:宋体">，这表示的是该数据类型指定的显示宽度，指定能够显示的数值中数字的个数。例如，假设声明一个</span><span style="font-family:"Times New Roman"">INT</span><span style="font-family:宋体">类型的字段：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">year INT(4)</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">该声明指明，在</span><span style="font-family:"Times New Roman"">year</span><span style="font-family:宋体">字段中的数据一般只显示</span><span style="font-family:"Times New Roman"">4</span><span style="font-family:宋体">位数字的宽度。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">在这里读者要注意：显示宽度和数据类型的取值范围是无关的。显示宽度只是指明</span><span style="font-family:"Times New Roman"">MySQL</span><span style="font-family:宋体">最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，假如向</span><span style="font-family:"Times New Roman"">year</span><span style="font-family:宋体">字段插入一个数值</span><span style="font-family:"Times New Roman"">19999</span><span style="font-family:宋体">，当使用</span><span style="font-family:"Times New Roman"">SELECT</span><span style="font-family:宋体">查询该列的值时候，</span><span style="font-family:"Times New Roman"">MySQL</span><span style="font-family:宋体">显示的将使完整的带有</span><span style="font-family:"Times New Roman"">5</span><span style="font-family:宋体">位数字的</span><span style="font-family:"Times New Roman"">19999</span><span style="font-family:宋体">，而不是</span><span style="font-family:"Times New Roman"">4</span><span style="font-family:宋体">位数字的值。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">其他整型数据类型也可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值，如【例</span><span style="font-family:"Times New Roman"">5.1</span><span style="font-family:宋体">】。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">【例</span><span style="font-family:"Times New Roman"">5.1</span><span style="font-family:宋体">】创建表</span><span style="font-family:"Times New Roman"">tmp1</span><span style="font-family:宋体">，其中字段</span><span style="font-family:"Times New Roman"">x</span><span style="font-family:宋体">，</span><span style="font-family:"Times New Roman"">y</span><span style="font-family:宋体">，</span><span style="font-family:"Times New Roman"">z</span><span style="font-family:宋体">，</span><span style="font-family:"Times New Roman"">m</span><span style="font-family:宋体">，</span><span style="font-family:"Times New Roman"">n</span><span style="font-family:宋体">数据类型依次为</span><tt><span style="font-size:12.0pt">TINYINT</span></tt><tt><span style="font-size:12.0pt">、SMALLINT、MEDIUMINT、INT、BIGINT,SQL语句如下：</span></tt></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">CREATE TABLE tmp1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">执行成功之后，是用</span><span style="font-family:"Times New Roman"">DESC</span><span style="font-family:宋体">查看表结构，结果如下：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">mysql> DESC tmp1;</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">+-------+-----------------+------+-----+----------+--------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">| Field | Type | Null | Key | Default | Extra |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">+-------+-----------------+------+-----+----------+--------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">| x | tinyint(4) | YES | | NULL | |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">| y | smallint(6) | YES | | NULL | |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">| z | mediumint(9) | YES | | NULL | |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">| m | int(11) | YES | | NULL | |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">| n | bigint(20) | YES | | NULL | |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">+-------+-----------------+-------+-----+----------+-------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"Times New Roman"">5 rows in set (0.00 sec)</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">可以看到，系统将添加不同的默认显示宽度。这些显示宽度能够保证显示每一种数据类型可以取到取值范围内的所有值。例如</span><span style="font-family:"Times New Roman"">TINYINT</span><span style="font-family:宋体">有符号数和无符号数的取值范围分别为</span><span style="font-family:"Times New Roman"">-128~127</span><span style="font-family:宋体">和</span><span style="font-family:"Times New Roman"">0~255</span><span style="font-family:宋体">，由于负号占了一个数字位，因此</span><span style="font-family:"Times New Roman"">TINYINT</span><span style="font-family:宋体">默认的显示宽度为</span><span style="font-family:"Times New Roman"">4</span><span style="font-family:宋体">。同理，其他整数类型的默认显示宽度与其有符号数的最小值的宽度相同。</span></span></span></p><h2 style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:宋体">不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此，应该根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。整数类型是不带小数部分的数值，现实生活中很多地方需要用到带小数的数值，下面将介绍</span></span><span style="font-size:10.5pt"><span style="font-family:"Times New Roman"">MySQL</span></span><span style="font-size:10.5pt"><span style="font-family:宋体">中支持的小数类型。</span></span></h2><p><img src="http://www.jbsage.com//attachment/20250807/211e624e2ee013bf0fce55691255dbd3.png" alt="211e624e2ee013bf0fce55691255dbd3.png" width="100%" height="auto"/></p>