tdsql mysql版数据类型 mysql数据类型选择

转载

mob64ca140f9cec 2024-06-11 05:59:37

文章标签 tdsql mysql版数据类型字段浮点数定点数 文章分类 MySQL 数据库

1）CHAR与VARCHAR

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

mysql> create table vc(v varchar(4),c char(4));
Query OK, 0 rows affected (0.01 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.01 sec)

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

MySQL的vachar字段的类型虽然最大长度是65535，但是并不是能存这么多数据，最大可以到65533（不允许非空字段的时候），当允许非空字段的时候只能到65532在允许空的时候，varchar(65532) will be 2 bytes (length) + up to 65532 chars (latin1) + 1 null byte（MYSQL COMPACT格式，每条记录有一个字节来表示NULL字段分布，如果表中有字段允许为空，则最大只能定到65532，如果没有字段允许为空，则那个字节可以节省，最大可以定义到65533）

编码长度限制：字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766，字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845，若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。

总结

原来MySQL的vachar字段的类型虽然最大长度是 65535，但是并不是能存这么多数据，最大可以到65533（不允许非空字段的时候），当允许非空字段的时候只能到65532。当一个列的值都是固定长度的时候，比如是10 ，此时用char(10),则只用char(10)所占的字节，但如果用varchar(10)，则所占的为varchar(11)所占的字节，因为 varchar需要在前面加上代表长度的字节。（同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)）。

不同存储引擎CHAR和VARCHAR使用原则

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

2）TEXT 与 BLOB

保存少量字符串时，会选择CHAR或者VARCHAR；而在保存大文本通常会选择使用TEXT或BLOB。二者之间的主要差别是BLOB能用来保存二进制数，比如照片；而TEXT只能保存字符数据，如文章。TEXT和BLOB中又包括 TEXT、MEDIUMTEXT、LONGTEXT和BLOB、MEDIUMBLOB、LONGBLOB三种不同类型，他们之间的主要区别是存储文本长度不同和存储字节不同。

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

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

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

合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。注意这种技术只能用于精确匹配的查询（散列值对于类似"<"或">="等范围搜索操作符是没有用处的）。可使用MD5(),SHA1(),CRC32()函数生成散列值，如果散列算法生成的字符串带有尾部空格，就不要把他们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。

tdsql mysql版数据类型 mysql数据类型选择_tdsql mysql版数据类型

查询记录时，可以通过相应的散列值来进行查询：

tdsql mysql版数据类型 mysql数据类型选择_浮点数_02

上面展示了合成索引的用法，由于只能用于精确匹配，在一定程度上减少了I/O，从而提高了查询效率。如果需要对BLOB或者TEXT字段进行模糊查询，Mysql提供了前缀索引，只为字段的前n列创建索引。

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

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

3）浮点数与定点数

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

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

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