MySQL入门12-数据类型

数据类型的作用：

Each data type has serveral characteristics:

• what kind of values it can represent?

• whether values are fixed-length (all values of the type take the same amount of space)

    or variable-length(the amount of space depends on the particular value being stored)

• how mysql compares and sorts values of the type

• whether the type can be indexed

数据类型分类

• 字符类型（字符串、二进制数据）

• 数值类型（整型、浮点型、定点型）

• 日期类型

数据类型前如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。 
(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，不知道这个m有什么用。

MySQL允许使用非标准语法：FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。这里，“(M,D)”表示该值一共显示M位整数，其中D位位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以 显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。

3、定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。 
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。 
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。 
3.char类型的字符串检索速度要比varchar类型的快。

varchar和text： 
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。 
2.text类型不能有默认值。 
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

5.二进制数据(_Blob)

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。 
2._BLOB存储的数据只能整体读出。 
3._TEXT可以指定字符集，_BLO不用指定字符集。

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

 

  

    MySQL的char,varchar,text,blob是几个有联系但是有有很大区别的字段类型，这算是mysql的基础吧，可是基础没有学好，恶补一下。

    MySQL 5.5 Reference Manual:

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. SeeSection D.10.4, “Limits on Table Column Count and Row Size”.

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.7, “Server SQL Modes”.

For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.

The following table illustrates the differences between CHAR and VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns (assuming that the column uses a single-byte character set such as latin1).

The values shown as stored in the last row of the table apply only when not using strict mode; if MySQL is running in strict mode, values that exceed the column length are not stored, and an error results.

先总结一下：

CHAR        定长，最大255个Bytes

VARCHAR     变长，最大65535个Bytes（既是单column的限制，又是row的限制）

TEXT        变长，字符集大对象，并根据字符集进行排序和校验，大小写不敏感

BLOB        变长，二进制大对象，大小写敏感

1、CHAR长度固定，VARCHAR是可变的

2、允许的最大长度不同

3、记录的内容不同，VARCHAR会额外记录一下字符串的长度。

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

    CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

    VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

    同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

VARCHAR需要额外记录字符串长度, prefix plus data：

1-byte    列声明的长度 < 255

2-byte    列声明的长度 >= 255

    VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行截断以使其适合。如果被截断的字符不是空格，则会产生一条警告。如果截断非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。

    这是什么意思呢？当插入列的值超过列的最大长度，采取的行为取决于 sql_mode

mysql> DESC t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1  | char(6)    | NO   |     |         |       |
| col3  | varchar(6) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO t1
    -> VALUES ('123456789', '123456789');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col1' at row 1 |
| Warning | 1265 | Data truncated for column 'col3' at row 1 |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)

    MySQL服务器可以以不同的SQL模式来操作，并且可以为不同客户端应用不同模式。这样每个应用程序可以根据自己的需求来定制服务器的操作模式。不同的sql_mode会影响所允许的数据类型。

    模式定义MySQL应支持哪些SQL语法，以及应执行哪种数据验证检查。这样可以更容易地在不同的环境中使用MySQL，并结合其它数据库服务器使用MySQL。

    你可以用--sql-mode="modes"选项启动mysqld来设置默认SQL模式。如果你想要重设，该值还可以为空(--sql-mode ="")。

    你还可以在启动后用SET [SESSION|GLOBAL] sql_mode='modes'语句设置sql_mode变量来更改SQL模式。设置 GLOBAL变量时需要拥有SUPER权限，并且会影响从那时起连接的所有客户端的操作。设置SESSION变量只影响当前的客户端。任何客户端可以随时更改自己的会话 sql_mode值。

# MySQL 默认的sql_mode = '';
mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)
# 此时，我们插入超过列长度的值，会进行截断，并warning。

# 如果我们把sql_mode 改为严格模式，就会报错
mysql> set sql_mode = 'strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| sql_mode      | STRICT_ALL_TABLES |
+---------------+-------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t1 VALUES ('123456789', '123456789');
ERROR 1406 (22001): Data too long for column 'col1' at row 1

    这里主要探讨CHAR与VARCHAR的存储方式。

    首先解释char，char是项目中常用的字段类型之一，它代表的含义是采用固定长度存储数据，换句话说，数据初始化的是就为该类型的字段分配固定长度的存储空间，即使没有达到存储空间的长度，实际占用的存储空间也是定义时的长度。举个例子来说，比如某字段 a   char(50)，指定的长度是50个Bytes的存储空间，那么当你存入一个字符串：'abc'的时候，实际上字符长度是3个Bytes，但是占用的硬盘空间还是50个Bytes。很显然，char的缺点就出来了：浪费存储空间！但是同时char的优点也显示出来了：固定长度，（索引）效率极高，不存在碎片。

    这里我们再探讨一下char的存储方式，虽然char会浪费极大的存储空间，但是你想过对于字符串的前后空格char是如何处理的吗？当存储的字符串没有达到char的最大长度时，字符串后面是不会以空格来填充的，而且char会过滤字符串末端的空格然后存储，而在比较字符串的时候又会自动空格填充到字符串的末端。

    varchar是存储可变长度的字符串，简单的说我们定义表机构的时候指定的字段长度是最大长度，当字符串没有达到最大长度的时候以字符串的实际长度来存储的，不占用多余的存储空间。因此，一般情况下，varchar比char节省存储空间，但是也经常有例外，后面接着探讨这个问题。

    一个特殊的情况是创建表的时候采用ROW_FORMAT=FIXED选项（默认的是ROW_FORMAT=DYNAMIC），那么mysql就会为每行数据分配固定长度的存储空间，当然这是特例。不知道你有没有想过：为什么有管理员愿意这样做呢？对varchar分配固定长度的存储空间是有道理的。举一个常见的例子，假设采用DYNAMIC默认选项，那么我们创建一个字段b  varchar(100)，现在我们插入一个只有10个Bytes的数据：abcdefghij，很好，只占用了10个Bytes的空间，相比char节省了不少存储空间。但是你想到问题了吗？比如某天以后你发现这个字段需要更新一下，更新为20个字符的数据：abcdefghijklmnopqrst，你知道数据库该如何存储吗？原先的存储位置分配的只有10个Bytes的空间，现在要存储20个Bytes，小于长度限制（最大长度是100个Bytes），问题就来了，mysql会如何处理呢？这里接下来可能要探讨mysql存储层面的分页机制或者拆分机制，就不再继续深入了。总之无论mysql采取什么方式，肯定会在磁盘上形成碎片，久而久之形成的磁盘碎片对系统效率是一个致命的打击，所以我们经常看到有管理员要把mysql导出然后导入，就是为了解决这个问题，提高效率。

       MySQL列的最大长度和所采用的字符集也会有关系，MySQL采用的计数单位是字节Bytes： 一个字符 不一定 等价于一个字节。务必注意这一点。

    上面我们提出来了一个问题：varchar比char节省存储空间，但是也经常有例外！对于这个疑问我们如何理解呢？要深入分析这个问题，我们需要再次深入了解varchar的存储机制。常用的中文存储一般采用gbk或者utf-8两种字符集，gbk每个字符占2个字节，utf-8每个字符占3个字节，所以：gbk字符集的最大存储长度是(65535-1-1)/2= 32766或者(65535-1-2)/2= 32766，这个算法的含义是：65535是varchar的最大长度，单位是 Bytes，第一个-1表示实际存储位置是从第二位开始的，第二个-1或者-2代表的含义是：varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度小于255时用1个字节，长度超过255时需要2个字节），除以2的原因是一个gbk字符集的字符占2个字节长度，所以根据情况可以得出gbk字符集的最大存储长度是32766。

那么UTF-8字符集呢？算法如下：(65535-1-1)/3=  21844或者(65535-1-2)/3= 21844减1减2的含义同上，除以3的含义是一个utf-8的字符集字符占用实际长度是3个字节。

mysql> CREATE TABLE t1(c1 varchar(65530) CHARACTER SET utf8);
ERROR 1074 (42000): Column length too big for column 'c1' (max = 21845); use BLOB or TEXT instead

    看完了这段你就能理解char(1)和varchar(1)占用的存储空间了吧？在这个极端情况下，很显然char更节省存储空间，因为char没有管理数据的额外开销。

     接下来，我们重点探讨一下varchar的65535存储长度代表的真正含义是什么？

因为前面说过一句话：既是单列的限制，又是整行的限制！我们详细来解释一下。对于单列的限制，我们举一个例子：create table tablename(c varchar(N)) charset=gbk;那么N的最大值是（65535-1-2）=32766。同时，如果同一行还有其他字段的话，那么所有字段的长度和不能超过65535，举个例子：create table tablename(c1 int(4), c2 char(30), c3 varchar(N) ) charset=utf8，那么N的最大值是（65535-1-2-4-30*3）/3=21812，也许你很理解int(4)需要4个字符的位置，这里可能需要更深入的了解int的存储机制，我不太了解，书还没有看呢。总之int类型占用4个字符的长度，换句话说create table tablename(c1 int(32), c2 char(30), c3 varchar(N) ) charset=utf8，N的最大值也是（65535-1-2-4-30*3）/3=21812。 下面我显示一下具体是什么意思。

column* varchar(N)    # N < 65535

length(column1) + length(column2) + length(column..) + .. < 65535

mysql> CREATE TABLE t1(c1 varchar(65500));            # 我们创建一个表，c1字段长度为65500
Query OK, 0 rows affected (0.03 sec)

mysql> DESC t1;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| c1    | varchar(65500) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 ADD COLUMN c2 varchar(50);      # 报错，Row size too large
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

废话了这么多，总之一句话：char在浪费存储空间的劣势下，获得了较高的效率，varchar相反。接下来我们也要总结一下什么情况下使用char，什么情况下使用varchar。

原则一：根据字符串长度确定，凡是固定长度的字符串或者类似固定长度的字符串一律用char。比如***号码，手机号码，银行卡号，MD5，哈希值等这是字符串是固定长度的，毫无疑问用char，还有一类是基本固定长度但是略有出入的，比如中国人的姓名等，一般长度可能是2~5个汉字，这类信息也非常适合用char来存储，只要分配一些略大于通常长度即可。

原则二：数据是否经常更改导致碎片，可能经常变动而产生存储碎片的小字符串一律用char。我们知道char类型的数据是一次性分配存储空间的，无论以后你怎么修改，数据始终在该存储空间内的，不会产生碎片。而varchar则不同，varchar的数据长度是可变的，当修改后的数据大于当前存储长度时，就会产生碎片，如果该应用是反复修改数据的应用，那么久而久之就是产生无数碎片，效率可想而知。

原则三：理解varchar的存储空间和内存空间的区别，合理指定varchar的长度。我们知道varchar的存储长度是根据字符串的长度而定的，但是运行时占用的内存空间却是按照定义的长度分配内存空间的（我的理解，不知道是否正确）。这个现象导致存储一个字符串，比如通信地址，通畅在100个字符内就能存储完成，于是varchar(100)是一个合理的选择，但是由于之前讲的，可能有人图方便使用varchar(500)，反正用的存储空间是一样的，但是效果确实不一样的。在内存模型中varchar(100)与varchar(500)是两码事，后者比前者占用多5倍的内存空间，在临时表和排序的时候这个差别几乎可能差一个数量级，于是效率可想而知。

BINARY & VARBINARY    

    BINARY和VARBINARY与前面介绍的CHAR和VARCHAR类型有点类似，不同的是BINARY和VARBINARY存储的是二进制的字符串，而非字符型字符串。也就是说，BINARY和VARBINARY没有字符集的概念，对其排序和比较都是按照二进制值进行对比。

    BINARY（N）和VARBINARY（N）中的N指的是字节长度，而非CHAR（N）和VARCHAR（N）中的字符长度。

BLOB & TEXT

    在大多数情况下，可以将BLOB类型的列视为足够大的VARBINARY类型的列。同样，也可以将TEXT类型的列视为足够大的VARCHAR类型的列。然而，BLOB和TEXT在以下几个方面又不同于VARBINARY和VARCHAR：

•  在BLOB和TEXT类型的列上创建索引时，必须制定索引前缀的长度。而VARCHAR和VARBINARY的前缀长度是可选的。

•  BLOB和TEXT类型的列不能有默认值。

•  在排序时只使用列的前max_sort_length个字节。

    Blob的优点在于不理会存储文件的类型，图片、文件、压缩包都按照二进制文件来处理存储；

缺点在于如果存储了超大文件（比如多媒体文件之类的），数据库的效率肯定会受影响。如果要存储超大文件，最好还是存储到服务器中，只在数据库中保留路径。或者将文件信息和文件内容分别存储在两个表中，只在需要读取文件内容的时候才查询操作文件内容表。

转载于:https://blog.51cto.com/skypegnu1/1581662