欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

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

程序员文章站 2022-04-11 17:07:16
...

一、选择合适的数据类型

（一）、CHAR与VARCHAR

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

1、二者区别

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

MySQL学习（三）：MySQL开发篇（1）——数据类型的选择及字符型介绍
- 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：被更多使用。

    • 不同存储引擎对其二者的使用：
    • MySQL学习（三）：MySQL开发篇（1）——数据类型的选择及字符型介绍

（二）、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。
MySQL学习（三）：MySQL开发篇（1）——数据类型的选择及字符型介绍

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、常用字符集比较

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

2、字符集的选择

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

（二）、MySQL字符集的设置

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

MySQL学习（三）：MySQL开发篇（1）——数据类型的选择及字符型介绍
- 以上三种使用默认的校对规则
- 查询当前服务器的字符集和校对规则：

show variables like 'chaeacter_set_server';

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

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

3、表字符集和校对规则

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

4、列字符集和校对规则

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

（三）、字符集的修改

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

上一篇： mysql数据类型的选择

下一篇： 河南省排名前十的本科大学有哪些?附河南最好的几所大学排名

推荐阅读