MySQL第8天

选择合适的数据类型

1 CHAR与VARCHAR

char属于固定长度的字符类型，而varchar属于可变长度的字符类型

 MyISAM 存储引擎:建议使用固定长度的数据列代替可变长度的数据列。
 MEMORY 存储引擎:目前都使用固定长度的数据行存储,因此无论使用 CHAR 或
VARCHAR 列都没有关系。两者都是作为 CHAR 类型处理。
 InnoDB 存储引擎:建议使用 VARCHAR 类型。对于 InnoDB 数据表,内部的行存储
本质上,使用固定长度的 CHAR 列不一定比使用可变长度 VARCHAR 列性能要好。因而,主
要的性能因素是数据行使用的存储总量。由于 CHAR 平均占用的空间多于 VARCHAR,因此使
用 VARCHAR 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的。

2 TEXT 与 BLOB

1 一般在保存少量字符串时，会用char或varchar；而在保存较大文本时，通常会选择使用TEXT BLOB

2  区别：BLOB可以保存二进制数据，而TEXT只能保存字符串数据

3  TEXT 和 BLOB 中有分别包括
TEXT、MEDIUMTEXT、LONGTEXT 和 BLOB、MEDIUMBLOB、LONGBLOB3 种不同的类型

4  建议定期使用OPTIMIZE TABLE 功能对这类表进行碎片整理

5 可以使用合成的索引来提高大文本字段的查询性能

合成索引就是根据大文本字段的内容建立一个散列值,并把这个值存储在单独的
数据列中,接下来就可以通过检索散列值找到数据行了。但是,要注意这种技术只能用于精
确匹配的查询

6  如果需要对 BLOB 或者 CLOB 字段进行模糊查询，MySQL也提供了前缀索引

浮点数与定点数

1 浮点数一般用于表示含有小数部分的数值，如果精度超过的话，将会四舍五入

2 定点数不同于浮点数，实际上是以字符串形式存放的，所以定点数可以更加精确

   如果插入的数值精度大于实际的定义的精度，MySQL会警告，但是还会四舍五入

3  在MySQL中，用decimal表示定点数

4 在选择浮点数时，要注意四舍五入的问题，保留足够的小数位

日期类型选择

 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年
份”,那么用 1 个字节来存储的 YEAR 类型完全可以满足,而不需要用 4 个字节来
存储的 DATE 类型。这样不仅仅能节约存储,更能够提高表的操作效率。
 如果要记录年月日时分秒,并且记录的年份比较久远,那么最好使用 DATETIME,
而不要使用 TIMESTAMP。因为 TIMESTAMP 表示的日期范围比 DATETIME 要短得多。
 如果记录的日期需要让不同时区的用户使用,那么最好使用 TIMESTAMP,因为日
期类型中只有它能够和实际时区相对应。

字符集

1   在节省内存和处理时间上都具有优势,这也是Unicode编码方式更流行的原因

2 选择合适的字符集

(1)满足应用支持语言的需求,如果应用要处理各种各样的文字,或者将发布到使用
不同语言的国家或地区,就应该选择 Unicode 字符集。对 MySQL 来说,目前就是 UTF-8。
(2)如果应用中涉及已有数据的导入,就要充分考虑数据库字符集对已有数据的兼容
性。假如已有数据是 GBK 文字,如果选择 GB2312-80 为数据库字符集,就很可能出现某些
文字无法正确导入的问题。
(3)如果数据库只需要支持一般中文,数据量很大,性能要求也很高,那就应该选择
双字节定长编码的中文字符集,比如 GBK。因为,相对于 UTF-8 而言,GBK 比较“小”,每
个汉字只占 2 个字节,而 UTF-8 汉字编码需要 3 个字节,这样可以减少磁盘 I/O、数据库 cache,
以及网络传输的时间,从而提高性能。相反,如果应用主要处理英文字符,仅有少量汉字数
据,那么选择 UTF-8 更好,因为 GBK、UCS-2、UTF-16 的西文字符编码都是 2 个字节,会造成
很大不必要的开销。
(4)如果数据库需要做大量的字符运算,如比较、排序等,选择定长字符集可能更好,
因为定长字符集的处理速度要比变长字符集的处理速度快。
(5)如果所有客户端程序都支持相同的字符集,应该优先选择该字符集作为数据库字
符集。这样可以避免因字符集转换带来的性能开销和数据损失。

3  MySQL的字符集包括字符集和校对规则

字符集是用来定义 MySQL 存储字符串的方式,

校对规则则是定义了比较字符串的方式

MySQL字符集的设置

服务器

1  服务器字符集的校对，在MySQL服务启动时确定

可以在 my.cnf 中设置:
[mysqld]
default-character-set=gbk
或者在启动选项中指定:
mysqld --default-character-set=gbk
或者在编译的时候指定:
./configure --with-charset=gbk

2  可以用“show variables like 'character_set_server';”命令查询当前服务器的字符集和校
对规则。

数据库字符集和校对规则

1  数据库的字符集和校对规则在创建数据库的时候指定,也可以在创建完数据库后通过“alter
database”命令进行修改。

2  需要注意的是,如果数据库里已经存在数据,因为修改字符集并不能将已有的数据按照新的字符集进行存放,

所以不能通过修改数据库的字符集直接修改数据的内容

3  要 显 示 当 前 数 据 库 的 字 符 集 和 校 对 规 则 , 可 以 使 用 “ show variables like '
character_set_database '”和“show variables like ' collation_database '”命令查看:

表字符集和校对规则

 如果指定了字符集和校对规则,使用指定的字符集和校对规则;
 如果指定了字符集没有指定校对规则,使用指定字符集的默认校对规则;
 如果没有指定字符集和校对规则,使用数据库字符集和校对规则作为表的字符集和
校对规则。

1  推荐在创建表的时候明确指定字符集和校对规则,避免受到默认值的影响

2 要显示表的字符集和校对规则,可以使用 show create table 命令查看:

连接字符集和校对规则

1对于客户端和服务器的交互操作,MylSQL 提供了 3 个不同的参数:character_set_client、
character_set_connection 和 character_set_results,分别代表客户端、连接和返回结果的字符
集通常情况下,这 3 个字符集应该是相同的,才可以确保用户写入的数据可以正确地读出,
特别是对于中文字符,不同的写入字符集和返回结果字符集将导致写入的记录不能正确读出。

2  通常情况下,不会单个地设置这 3 个参数,可以通过以下命令:

     SET NAMES ***;

   来设置连接的字符集和校对规则,这个命令可以同时修改这 3 个参数的值.使用这个方法修
改连接的字符集和校对规则,需要应用每次连接数据库后都执行这个命令。

3  一个更简便的办法,是在 my.cnf 中设置以下语句:

   [mysql]
default-character-set=gbk

字符集的修改步zou