MySQL学习（三）：MySQL开发篇（1）——数据类型的选择及字符型介绍

一、选择合适的数据类型

（一）、CHAR与VARCHAR

一般用于保存少量字符串。

1、二者区别

• 下表显示了各种字符串值保存到CHAR(4)与VARCHAR(4)列后的结果，来说明二者之间的差别。

- CHAR(n)：定长数据格式，n为能存储的最大字节长度；当存入字符个数小于n时，末尾0补全；当存入字符个数大于n时，截取n个字符；当存储首尾带空格的值时，会自动删除首尾空格。
- VARCHAR(n)：变长数据格式，长度为n个字节的可变长度且非Unicode的字符数据；当存入字符个数小于n时，以实际使用为准；当存入字符个数大于n时，截取n个字符；

CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
INSERT INTO vc VALUES('ab  ', 'ab  ');
INSERT INTO vc VALUES('c', 'c');
SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;
+----------------+----------------+
| CONCAT(v, '+') | CONCAT(c, '+') |
+----------------+----------------+
| ab  +          | ab+            |
| c+             | c+             |
+----------------+----------------+

2、优缺点及使用

• 一般性建议：

  • CHAR的优缺点:

    • 优点： 由于固定长度，处理速度比较快
    • 缺点：浪费存储空间，程序员需对行尾空格进行处理。
    • 用处：对于长度变化不大且对查询速度有较高需求的数据可考虑用其。

  • VARCHAR：被更多使用。

  • 不同存储引擎对其二者的使用：
  • 

（二）、TEXT与BLOB

一般用于保存较大文本

1、二者异同

• 相同点：
  
  • 用于保存较大文本
• 不同点：
  
  • 保存数据格式：
    
    • BLOB：用于保存二进制数据，eg: 照片。
    • TEXT：用于保存字符数据，eg: 文章或日记。
  • 包括以下类型：
    
    • BLOB：BLOB、MEDIUMBLOB、LONGBLOB。
    • TEXT：TEXT、MEDIUMTEXT、LONGTEXT。
    • 存储文本长度、存储字节均不同。

2、二者常见问题

• （1）、会引起一些性能问题，特别是执行大量删除操作时。
  
  • 影响：输出表中数据后，表的文件大小不变；删除会留下大量“空洞”，后面填入这些“空洞”的记录在插入的性能上会有影响。
  • 解决方案：定期使用OPTIMIZE TABLE功能对这类表进行碎片整理
• （2）、可以使用合成的（Synthetic）索引提高大文本字段的查询性能。
  
  • 合成索引：根据大文本字段的内容建立一个散列值， 并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。此技术只能用于精确匹配的查询。
    
    • 计算散列值的方法：MD5(str)、SHA1(str)、CRC32(expr)或使用自己的应用程序逻辑，记住：数值型散列值可以很高效率地存储。

CREATE TABLE sys (id VARCHAR(100), context BLOB, hash_value VARCHAR(40));
INSERT INTO sys VALUES(1, REPEAT('beijing',2), MD5(context));
INSERT INTO sys VALUES(2, REPEAT('beijing 2008',2), MD5(context));
SELECT * FROM sys WHERE hash_value=MD5(REPEAT('beijing 2008',2)); 
+----+--------------------------+----------------------------------+
| id | context                  | hash_value                       |
+----+--------------------------+----------------------------------+
| 2  | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+----+--------------------------+----------------------------------+

• 若需要对BLOB或CLOB字段进行模糊查询，MySQL提供了前缀索引。

CREATE INDEX idx_blob ON sys(context(100));
DESC SELECT * FROM sys WHERE context like 'beijing%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | sys   | ALL  | idx_blob      | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

• （3）、在不必要时，避免检索大型的BLOB或TEXT的值。
• （4）、把BLOB或TEXT分离到单独的表中。

（三）、浮点数与定点数

MySQL中，float、double(或real)用来表示浮点数；decimal或numberic用来表示定点数。

1、二者特点

• 浮点型：float(m[, n])，m为“总位数”，n为“小数位数”，“截取+四舍五入”保存；若无小数位数限制，按照“总位数-1”取数据。
  
  • 有时使用单精度浮点数表示，会产生误差。eg：float(10,2)，存入131072.32，查询时发现变成131072.31。
  • float(m, n)：非标准用法，若用于数据库的迁移，不要使用。
  • 数据长度<=m。
• 定点性：以字符串形式存放，更精确地保存数据；若实际插入值大于定义的精度，
  
  • 默认的SQLMode下MySQL会警告，但数据按照实际精度四舍五入后插入；
  • 传统的TRADITIONAL下，系统会直接报错，导致数据无法插入。

2、二者使用

• 使用二者时，考虑问题：
  
  • 浮点数存在精度问题；
  • 对货币等对精度敏感的数据，应使用定点数标书或存储；
  • 编程中，若用浮点数，特别注意误差问题，并尽量避免做浮点数比较；
  • 注意浮点数中一些特殊值的处理。

（四）、日期类型

MySQL中，常用的日期类型有DATE、TIME、DATETIME和TIMESTAMP。

1、类型特点

• 若超出有限制范围，默认的SQLMode下，系统会报错，并以“零值”进行存储；

+-------------+--------------------+
| DATETIME    | 0000-00-00 00:00:00|
+-------------+--------------------+
| DATE        | 0000-00-00         | 
+-------------+--------------------+
| TIMESTAMP   | 00000000000000     |
+-------------+--------------------+
| TIME        | 00:00:00           | 
+-------------+--------------------+
| YEAR        | 0000               |
+-------------+--------------------+

• TIMESTAMP：第一个默认值CURRENT_TIMESTAMP，系统时间（插入NULL时为其）。第二个TIMESTAMP字段默认为0
  
  • 多个TIMESTAMP时，仅一列的默认值为CURRENT_TIMESTAMP
  • 时区相关：插入时，转化为本地时区存放；取出时，需将日期转化为本地时区后显示。

2、选择原则

• 根据实际需求选择能够满足应用的最小存储的日期类型。
  
  • 若应用只需记录“年份”，用一个字节存储的YEAR足够；
  • 节约存储，提高表的操作效率。
    
    • 若记录年月日，通常使用DATE。
    • 若记录时分秒，通常使用TIME。
    • 若记录年月日时分秒，且记录年份久远，最好使用DATETIME。
    • TIMESTAMP表示的日期范围要短很多。
    • 若记录的日期需让不同时区的用户使用，最好使用TIMESTAMP。
    • 仅TIMESTAMP可以和实际时区对应。
    • 若经常插入或更新日期为当前系统时间，通常使用TIMESTAMP。
    • TIMESTAMP值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为19个字符。
    • 若想获得数字值，应在 TIMESTAMP列添加“+0”。
      -

二、字符集

（一）、字符集

1、常用字符集比较

2、字符集的选择

• 满足应用支持语言的需求，若应用处理各种各样的文字，或将发布到使用不同语言的国家或地区，选择Unicode字符集。对MySQL来说，目前是UTF-8；
• 若应用涉及到已有数据导入，需充分考虑数据库字符集对已有数据的兼容性；
• 若数据库仅需支持一般中文，性能要求也高，应选择双字节定长编码的中文字符集，eg: GBK；（编码2个字节，UCS-2，UTF-8）
• 若数据库主要处理英文字符，仅少量汉字数据，应选择UTF-8；（编码3个字节）
• 若数据库需要做大量的字符运算（eg: 比较、排序），选择定长字符集更好；（比变长的处理快）
• 若所有客户端都支持相同的字符集，优先选择该字符集作为该数据库字符集。

（二）、MySQL字符集的设置

1、服务器字符集和校对规则

- 以上三种使用默认的校对规则
- 查询当前服务器的字符集和校对规则：

show variables like 'chaeacter_set_server';

2、数据库字符集和校对规则

• 创建时指定；CHARSET
• alter database 修改；

3、表字符集和校对规则

• 创建时指定；CHARSET
• alter database 修改；

4、列字符集和校对规则

5、连接字符集和校对规则

（三）、字符集的修改