• 8. 选择合适的数据类型


    8.1 CHAR与VARCHAR

    ​ CHAR 和 VARCHAR 类型类似,都用来存储字符串,但它们保存和检索的方式不同。CHAR 属于固定长度的字符类型,而 VARCHAR 属于可变长度的字符类型。当char定义一定宽度的格式时,数据末尾的空格会被处理掉。char是固定长度的,处理速度比varchar块,但缺点是浪费空间。

    char和varchar如何选择?

    在 MySQL 中,不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同,这里简单概括如下。

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

    8.2 TEXT与BLOB

    ​ char或者varchar适用于保存少量字符串,如果保存较大文本是,通常会选择使用TEXT和BLOB。

    BLOB:用来保存二进制数据,比如照片。

    TEXT:只能保存字符数据,比如一篇文章和日记。

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

    (1)BLOB 和 TEXT 值会引起一些性能问题,特别是在执行了大量的删除操作时。 删除操作会在数据表中留下很大的“空洞”,以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理,避免因为“空洞”导致性能问题。

    (2)可以使用合成的(Synthetic)索引提高大文本字段(BLOB 或 TEXT)的查询性能

    简单来说,合成索引就是根据大文本字段的内容建立一个散列值,并把这个值存储在单独的数据列中,接下来就可以通过检索散列值找到数据行了。但是,要注意这种技术只能用于精确匹配的查询(散列值对于类似<或>=等范围搜索操作符是没有用处的)。

    mysql>  create table t (id varchar(100),context  blob,hash_value varchar(40)); 
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert into t values(1,repeat('beijing',2),md5(context));
    Query OK, 1 row affected (0.01 sec)
    
    mysql>  insert into t values(2,repeat('beijing',2),md5(context));
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into t values(3,repeat('beijing 2008',2),md5(context));
    Query OK, 1 row affected (0.00 sec)
    
    mysql>  select * from t;
    +------+--------------------------+----------------------------------+
    | id   | context                  | hash_value                       |
    +------+--------------------------+----------------------------------+
    | 1    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
    | 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
    | 3    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
    +------+--------------------------+----------------------------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));
    +------+--------------------------+----------------------------------+
    | id   | context                  | hash_value                       |
    +------+--------------------------+----------------------------------+
    | 3    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
    +------+--------------------------+----------------------------------+
    

    (3)对 BLOB 或者 CLOB 字段进行模糊查询,MySQL 提供了前缀索引,也就是只为字段的前 n 列创建索引。

    mysql> create index idx_blob on t(context(100));
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc select * from t where context like 'beijing%' G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t
       partitions: NULL
             type: ALL
    possible_keys: idx_blob
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 3
         filtered: 100.00
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    # 请注意,这里的查询条件中,“%”不能放在最前面,否则索引将不会被使用。
    

    (4)在不必要的时候避免检索大型的 BLOB 或 TEXT 值。

    (5)把 BLOB 或 TEXT 列分离到单独的表中。

    8.3 浮点数与定点数

    浮点数一般用于表示含有小数部分的数值。插入数据超出实际精度,则插入值会被四舍五入到实际定义的精度值,然后插入,四舍五入的过程不会报错。在 MySQL 中 float、double(或 real)用来表示浮点数。

    定点数实际上是以字符串形式存放的,定点数可以更加精确的保存数据。如果实际插入的数值精度大于实际定义的精度,则 MySQL 会进行警告(默认的 SQLMode 下),但是数据按照实际精度四舍五入后插入;如果 SQLMode 是在 TRADITIONAL传统模式下,则系统会直接报错,导致数据无法插入。在 MySQL 中,decimal(或 hnumberic)用来表示定点数。

    注意:在今后关于浮点数和定点数的应用中,用户要考虑到以下几个原则:

    • 浮点数存在误差问题;
    • 对货币等对精度敏感的数据,应该用定点数表示或存储;
    • 在编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较
    • 要注意浮点数中一些特殊值的处理。

    8.4 日期类型选择

    选择日期类型的原则:

    • 如果只需要记录年份,只需要用一个字节的YEAR类型完全可以满足,而不需要四个字节的DATE类型。
    • 如果要记录年月日时分秒,并且记录年份比较久远,那么最好使用DATETIME
    • 如果要记录的日期要让不同时区的用户使用,那么最好使用TIMESTAMP

    延展阅读

    mysql的严格模式和宽松模式

    mysql支持的sql_mode模式:ANSI、TRADITIONAL、STRICT_ALL_TABLES和STRICT_TRANS_TABLES。

    ANSI模式:宽松模式,对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。

    TRADITIONAL模式:严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误。用于事务时,会进行事务的回滚。

    STRICT_TRANS_TABLES模式:严格模式,进行数据的严格校验,错误数据不能插入,报error错误。只对支持事务的表有效。

    STRICT_ALL_TABLES模式:严格模式,进行数据的严格校验,错误数据不能插入,报error错误。对所有表都有效。

    # 查看当前模式
    select @@sql_mode;
    show variables like '%mode%';
    # 更改模式值:(1)命令行 (2)配置文件 更多详情见官网
    # 有时候当像数据库插入一个date类型为空或者不允许插入的字符的时候,可以通过设置sql_mode来允许操作
    # 清空模式值[局部]
    set [global | session] sql_mode = "";
    
  • 相关阅读:
    python3.4+pyspider爬58同城(二)
    pyspider安装后,点击run,报pyhton has stop working或python已停止运行的错误
    PermissionError: [WinError 32] 另一个程序正在使用此文件,进程无法访问。: 'C:\Users\video\AppData\Local\Temp\tmpfipzk8ma'--问题解决
    使用firefoxprofile,selenium设置firefox,初始化firefox
    排序算法讲解
    Java寫聊天小程序
    csproj项目工程文件的脚本/动态链接库设置
    常见的内存加密防破解及安全方案
    Animator直接引用FBX下的AnimClip与直接引用单独的AnimClip的对比
    Jupyter多内核的手动配置(Python多版本)
  • 原文地址:https://www.cnblogs.com/wubug/p/13667178.html
Copyright © 2020-2023  润新知