玖叶教程网

前端编程开发入门

2024年04月20日

第8章 - 教你学会MySql选择合适的数据类型

第8章 选择合适的数据类型


在使用MySQL创建数据表时都会遇到一个问题，如何为字段选择合适的数据类型。例如，创建一张员工表用来记录员工的信息，这时对员工的各种属性如何来进行定义？也许大家会想，这个问题很简单，每个字段可以使用很多种数据类型来定义，比如int、float、double、decimal等。其实正因为可选择的数据类型太多，才需要依据一些原则来“挑选”最适合的数据类型。本章将详细介绍字符、数值、日期数据类型的一些选择原则。

8.1 CHAR与VARCHAR

CHAR和VARCHAR类型类似，都用来存储字符串，但它们保存和检索的方式不同。CHAR 属于固定长度的字符类型，而VARCHAR属于可变长度的字符类型。

表8-1显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别。

请注意表8-1中最后一行的值只适用非“严格模式”时，如果MySQL运行在严格模式，超过列长度的值将不会保存，并且会出现错误提示，关于“严格模式””将在第16章的SQL MODE及其相关问题的章节中详细介绍。

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同，因为检索时从CHAR列删除了尾部的空格。下面通过一个例子说明该差别：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));

Query OK, 0 rows affected (0.02 sec)


mysql> INSERT INTO vc VALUES ('ab ', 'ab ');

Query OK, 1 row affected (0.00 sec)


mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;

+----------------+----------------+

| CONCAT(v, '+') | CONCAT(c, '+') |

+----------------+----------------+

| ab + | ab+ |

+----------------+----------------+

1 row in set (0.00 sec)

由于CHAR是固定长度的，所以它的处理速度比VARCHAR快得多，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储。

另外，随着MySQL版本的不断升级，VARCHAR数据类型的性能也在不断改进并提高，所以在许多的应用中，VARCHAR类型被更多地使用。


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

l MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列。

l MEMORY存储引擎：目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理。

l 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种不同的类型，它们之间的主要区别是存储文本长度不同和存储字节不同，用户应该根据实际情况选择能够满足需求的最小存储类型。本节主要对BLOB和TEXT存在的一些常见问题进行介绍。

? BLOB和TEXT值会引起一些性能问题，特别是在执行了大量的删除操作时。

删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。

下面的例子描述了OPTIMIZE TABLE的碎片整理功能。

（1）创建测试表t，字段id和context的类型分别为varchar(100)和text：

mysql> create table t (id varchar(100),context text);

Query OK, 0 rows affected (0.01 sec)


（2）往t中插入大量记录，这里使用repeat函数插入大字符串：

mysql> insert into t values(1,repeat('haha',100));

Query OK, 1 row affected (0.00 sec)


mysql> insert into t values(2,repeat('haha',100));

Query OK, 1 row affected (0.00 sec)


mysql> insert into t values(3,repeat('haha',100));

Query OK, 1 row affected (0.00 sec)


mysql> insert into t select * from t;
…
mysql> insert into t select * from t;

Query OK, 196608 rows affected (4.86 sec)

Records: 196608 Duplicates: 0 Warnings: 0

mysql> exit

Bye

（3）退出到操作系统下，查看表t的物理文件大小：

[bjguan@zzx test]$ du -sh t.*

16K t.frm

155M t.MYD

8.0K t.MYI


这里数据文件显示为155MB。

（4）从表t中删除id为“1”的数据，这些数据占总数据量的1/3：

[bjguan@localhost test]$ mysql -u root -p1234

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 24 to server version: 5.0.45-log


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> use test

Database changed

mysql> delete from t where id=1;

Query OK, 131072 rows affected (4.33 sec)


mysql> exit

Bye

（5）再次退出到操作系统下，查看表t的物理文件大小：

[bjguan@zzx test]$ du -sh t.*

16K t.frm

155M t.MYD

8.0K t.MYI

可以发现，表t的数据文件仍然为155MB，并没有因为数据删除而减少。

（6）接下来对表进行OPTIMIZE（优化）操作：

[bjguan@localhost test]$ mysql -u root -p1234

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 24 to server version: 5.0.45-log


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> use test;

Database changed

mysql> OPTIMIZE TABLE t;

+--------+----------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------+----------+----------+----------+

| test.t | optimize | status | OK |

+--------+----------+----------+----------+

1 row in set (2.88 sec)


mysql> exit

Bye

（7）再次查看表t的物理文件大小：

[bjguan@localhost test]$ du -sh t.*

16K t.frm

104M t.MYD

8.0K t.MYI

可以发现，表的数据文件大大缩小，“空洞”空间已经被回收。


? 可以使用合成的（Synthetic）索引来提高大文本字段（BLOB或TEXT）的查询性能。

简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。

下面通过实例介绍一下合成索引的使用方法。

（1）创建测试表t，字段id、context、hash_value字段类型分别为varchar(100)、blob、varchar(40)：

mysql> create table t (id varchar(100),context blob,hash_value varchar(40));

Query OK, 0 rows affected (0.03 sec)


（2）t中插入测试数据，其中hash_value用来存放context列的MD5散列值：

mysql> insert into t values(1,repeat('beijing',2),md5(context));

Query OK, 1 row affected (0.00 sec)


mysql> insert into t values(2,repeat('beijing',2),md5(context));

Query OK, 1 row affected (0.00 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)


（3）如果要查询context值为“beijing 2008beijing 2008”的记录，可以通过相应的散列值来查询：

mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));

+------+--------------------------+----------------------------------+

| id | context | hash_value |

+------+--------------------------+----------------------------------+

| 3 | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |

+------+--------------------------+----------------------------------+

1 row in set (0.00 sec)

上面的例子展示了合成索引的用法，由于这种技术只能用于精确匹配，在一定程度上减少I/O，从而提高查询效率。如果需要对BLOB或者CLOB字段进行模糊查询，MySQL提供了前缀索引，也就是只为字段的前n列创建索引，举例如下：

mysql> create index idx_blob on t(context(100));

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0


mysql> desc select * from t where context like 'beijing%' \G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t

type: range

possible_keys: idx_blob

key: idx_blob

key_len: 103

ref: NULL

rows: 2

Extra: Using where

1 row in set (0.00 sec)

可以发现，对context前100个字符进行模糊查询，就可以用到前缀索引。请注意，这里的查询条件中，“%”不能放在最前面，否则索引将不会被使用。

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

例如，SELECT * 查询就不是很好的想法，除非能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，很可能毫无目的地在网络上传输大量的值。这也是BLOB或TEXT标识符信息存储在合成的索引列中对用户有所帮助的例子。用户可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索BLOB或TEXT值。

? 把BLOB或TEXT列分离到单独的表中。

在某些环境中，如果把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

8.3 浮点数与定点数

浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。在MySQL中float、double（或real）用来表示浮点数。

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

在简单了解了浮点数和定点数的区别之后，来看一个例子，回顾一下前面讲到的浮点数精确性问题。

mysql> create table t (f float( 8,1));

Query OK, 0 rows affected (0.03 sec)

mysql> desc t;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| f | float(8,1) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> insert into t values (1.23456);

Query OK, 1 row affected (0.00 sec)


mysql> select * from t;

+------+

| f |

+------+

| 1.2 |

+------+

1 row in set (0.00 sec)


mysql> insert into t values (1.25456);

Query OK, 1 row affected (0.00 sec)


mysql> select * from t;

+------+

| f |

+------+

| 1.2 |

| 1.3 |

+------+

2 rows in set (0.00 sec)

从上面的例子中，可以发现对于第一次插入值1.23456到float（8,1）时，该值被截断，并保存为1.2，而第二次插入值1.25456到float（8,1）时，该值进行了四舍五入然后被截断，并保存为1.3，所以在选择浮点型数据保存小数时，要注意四舍五入的问题，并尽量保留足够的小数位，避免存储的数据不准确。

为了能够让大家了解浮点数与定点数的区别，再来看一个例子：

mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));

Query OK, 0 rows affected (0.29 sec)

mysql> insert into test values(131072.32,131072.32);

Query OK, 1 row affected (0.07 sec)

mysql> select * from test;

+-----------+-----------+

| c1 | c2 |

+-----------+-----------+

| 131072.31 | 131072.32 |

+-----------+-----------+

1 row in set (0.00 sec)

从上面的例子中可以看到，c1列的值由131072.32变成了131072.31，这是上面的数值在使用单精度浮点数表示时，产生了误差。这是浮点数特有的问题。因此在精度要求比较高的应用中（比如货币）要使用定点数而不是浮点数来保存数据。

另外，浮点数的比较也是一个普遍存在的问题，下面的程序片断中对两个浮点数做减法运算：

public class Test {

public static void main(String[] args) throws Exception {

System.out.print("7.22-7.0=" + (7.22f-7.0f));

}

}

对上面Java程序的输出结果可能会想当然的认为是0.22，但是，实际结果却是7.22-7.0=0.21999979，因此，在编程中应尽量避免浮点数的比较，如果非要使用浮点数比较则最好使用范围比较而不要使用“==”比较。

再看一下使用定点数来实现上面的例子：

import java.math.BigDecimal;

/*

* 提供精确的减法运算。

* @param v1

* @param v2

*/


public class Test {


public static void main(String[] args) throws Exception {


System.out.print("7.22-7.0=" + subtract(7.22,7.0));

}


public static double subtract(double v1, double v2) {


BigDecimal b1 = new BigDecimal(Double.toString(v1));


BigDecimal b2 = new BigDecimal(Double.toString(v2));


return b1.subtract(b2).doubleValue();

}

}

上面的实例使用Java的BigDecimal类实现了定点数的精确计算，所以7.22减7.0的结果和预想的相同，为7.22-7.0=0.22。


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

? 浮点数存在误差问题；

? 对货币等对精度敏感的数据，应该用定点数表示或存储；

? 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

? 要注意浮点数中一些特殊值的处理。

8.4 日期类型选择

MySQL提供的常用日期类型有DATE、TIME 、DATETIME、TIMESTAMP，它们之间的区别在第3章中已经进行过详细论述，这里就不再赘述。下面主要总结一下选择日期类型的原则。

l 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全可以满足，而不需要用4个字节来存储的DATE类型。这样不仅仅能节约存储，更能够提高表的操作效率。

l 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多。

l 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。


8.5 小结

本章中主要介绍了常见数据类型的选择原则，简单归纳如下。

l 对于字符类型，要根据存储引擎来进行相应的选择。

l 对精度要求较高的应用中，建议使用定点数来存储数值，以保证结果的准确性。

l 对含有TEXT和BLOB字段的表，如果经常做删除和修改记录的操作要定时执行OPTIMIZE TABLE功能对表进行碎片整理。

l 日期类型要根据实际需要选择能够满足应用的最小存储的日期类型。

标签：mysqlchar 

作者:gojiuye , 分类:技术精选 , 浏览:18 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言