mysql字符串类型

1. mysql中常见的数据类型

一：MySQL数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的

MySQL支持多种数据类型，大致可以分为三类：数值 日期/时间和字符串

二、数值类型

1.整数类型

2. 高性能MySQL：字符串类型（2）

字符串类型（ ）

与CHAR 和VARCHAR 类似的类型还有BINARY 和VARBINARY 它们存储的是二进制字符串 二进制字符串跟常规字符串非常相似 但是二进制字符串存储的是字节码而不是字符 填充也不一样 MySQL 填充BINARY 采用的是 （零字节）而不是空格 在检索时也不会去掉填充值

当需要存储二进制数据 并且希望MySQL 使用字节码而不是字符进行比较时 这些类型是非常有用的 二进制比较的优势并不仅仅体现在大小写敏感上 MySQL 比较BINARY 字符串时 每次按一个字节 并且根据该字节的数值进行比较 因此 二进制比较比字符比较简单很多 所以也就更快

慷慨是不明智的

使用VARCHAR（ ） 和VARCHAR（ ） 存储 hello 的空间开销是一样的 那么使用更短的列有什么优势吗？

事实证明有很大的优势 更大的列会消耗更多的内存 因为MySQL 通常会分配固定大小的内存块来保存内部值 尤其是使用内存临时表进行排序或操作时会特别糟糕 在利用磁盘临时表进行排序时也同样糟糕

所以最好的策略是只分配真正需要的空间

BLOB 和TEXT 类型

BLOB 和TEXT 都是为存储很大的数据而设计的字符串数据类型 分别采用二进制和字符方式存储

实际上 它们分别属于两组不同的数据类型家族 字符类型是TINYTEXT SMALLTEXT TEXT MEDIUMTEXT LONGTEXT ;对应的二进制类型是TINYBLOB SMALLBLOB BLOB MEDIUMBLOB LONGBLOB BLOB 是SMALLBLOB 的同义词 TEXT 是SMALLTEXT 的同义词

与其他类型不同 MySQL 把每个BLOB 和TEXT 值当作一个独立的对象处理 存储引擎在存储时通常会做特殊处理 当BLOB 和TEXT 值太大时 InnoDB 会使用专门的 外部

存储区域来进行存储 此时每个值在行内需要 ~ 个字节存储一个指针 然后在外部存储区域存储实际的值

BLOB 和TEXT 家族之间仅有的不同是BLOB 类型存储的是二进制数据 没有排序规则或字符集 而TEXT 类型有字符集和排序规则

MySQL 对BLOB 和TEXT 列进行排序与其他类型是不同的 它只对每个列的最前max_sort_length 字节而不是整个字符串做排序 如果只需要排序前面一小部分字符 则可以减小max_sort_length 的配置 或者使用ORDER BY SUSTRING（column length）

MySQL 不能将BLOB 和TEXT 列全部长度的字符串进行索引 也不能使用这些索引消除排序 （关于这个主题下一章会有更多的信息 ）

磁盘临时表和文件排序

因为Memory 引擎不支持BLOB 和TEXT 类型 所以 如果查询使用了BLOB 或TEXT列并且需要使用隐式临时表 将不得不使用MyISAM 磁盘临时表 即使只有几行数据也是如此（Percona Server 的Memory 引擎支持BLOB 和TEXT 类型 但直到本书写作之际 同样的场景下还是需要使用磁盘临时表）

这会导致严重的性能开销 即使配置MySQL 将临时表存储在内存块设备上（RAMDisk） 依然需要许多昂贵的系统调用

最好的解决方案是尽量避免使用BLOB 和TEXT 类型 如果实在无法避免 有一个技巧是在所有用到BLOB 字段的地方都使用SUBSTRING（column length） 将列值转换为字符串（在ORDER BY 子句中也适用） 这样就可以使用内存临时表了 但是要确保截取的子字符串足够短 不会使临时表的大小超过max_heap_table_size 或tmp_table_size 超过以后MySQL 会将内存临时表转换为MyISAM 磁盘临时表

最坏情况下的长度分配对于排序的时候也是一样的 所以这一招对于内存中创建大临时表和文件排序 以及在磁盘上创建大临时表和文件排序这两种情况都很有帮助 例如 假设有一个 万行的表 占用几个GB 的磁盘空间 其中有一个utf 字符集的VARCHAR（ ） 列 每个字符最多使用 个字节 最坏情况下需要 字节的空间 如果在ORDER BY 中用到这个列 并且查询扫描整个表 为了排序就需要超过 GB 的临时表

这三行数据实际存储为整数 而不是字符串 可以通过在数字上下文环境检索看到这个双重属性

返回目录 高性能MySQL

编辑推荐

ASP NET MVC 框架揭秘

Oracle索引技术

ASP NET开发培训视频教程

lishixin/Article/program/MySQL/201311/29686

3. MySql数据类型有哪些

1. MySQL数据类型
在MySQL中有如下几种数据类型：
（1）数值型
数值是诸如32 或153.4 这样的值。MySQL 支持科学表示法，科学表示法由整数或浮点数后跟“e”或“E”、一个符号（“+”或“-”）和一个整数指数来表示。1.24E+12 和23.47e-1 都是合法的科学表示法表示的数。而1.24E12 不是合法的，因为指数前的符号未给出。
浮点数由整数部分、一个小数点和小数部分组成。整数部分和小数部分可以分别为空，但不能同时为空。
数值前可放一个负号“-”以表示负值。
（2）字符（串）型
字符型（也叫字符串型，简称串）是诸如“Hello, world!”或“一个馒头引起的血案”这样的值，或者是电话号码87398413这样的值。既可用单引号也可用双引号将串值括起来。
初学者往往分不清数值87398143和字符串87398143的区别。都是数字啊，怎么一 个要用数值型，一个要用字符型呢？关键就在于：数值型的87398143是要参与计算的，比如它是金融中的一个货款总额；而字符型的87398143是不 参与计算的，只是表示电话号码，这样的还有街道号码、门牌号码等等，它们都不参与计算。
（3）日期和时间型
日期和时间是一些诸如“2006-07-12”或“12:30:43”这样的值。MySQL还支持日期/时间的组合，如“2006-07-12 12:30:43”。
（4）NULL值
NULL表示未知值。比如填写表格中通讯地址不清楚留空不填写，这就是NULL值。
我们用Create Table语句创建一个表（参看前面的章节），这个表中包含列的定义。例如我们在前面创建了一个joke表，这个表中有content和writer两个列：
定义一个列的语法如下：
其中列名由col_name 给出。列名可最多包含64个字符，字符包括字母、数字、下划线及美元符号。列名可以名字中合法的任何符号（包括数字）开头。但列名不能完全由数字组成，因 为那样可能使其与MySQL数据类型分不开。MySQL保留诸如SELECT、DELETE和CREATE这样的词，这些词不能用做列名，但是函数名（如POS 和MIN）是可以使用的。
列类型col_type表示列可存储的特定值。列类型说明符还能表示存放在列中的值的最大长 度。对于某些类型，可用一个数值明确地说明其长度。而另外一些值，其长度由类型名蕴含。例如，CHAR(10) 明确指定了10个字符的长度，而TINYBLOB值隐含最大长度为255个字符。
有的类型说明符允许指定最大的显示宽度（即显示值时使用多少个字符）。浮 点类型允许指定小数位数，所以能控制浮点数的精度值为多少。
可以在列类型之后指定可选的类型说明属性，以及指定更多的常见属性。属性起修饰类型的作用，并更改其处理列值的方式，属性有以下类型：
（1）专用属性用于指定列。例如，UNSIGNED 属性只针对整型，而BINARY属性只用于CHAR 和VARCHAR。
（2）通用属性除少数列之外可用于任意列。可以指定NULL 或NOT NULL 以表示某个列是否能够存放NULL。还可以用DEFAULT，def_value 来表示在创建一个新行但未明确给出该列的值时，该列可赋予值def_value。def_value 必须为一个常量；它不能是表达式，也不能引用其他列。不能对BLOB 或TEXT 列指定缺省值。
如果想给出多个列的专用属性，可按任意顺序指定它们，只要它们跟在列类型之后、通用属性之前即可。类似地，如果需要给出多个通用属性，也可按任意顺序给出它们，只要将它们放在列类型和可能给出的列专用属性之后即可。
2. MySQL的列（字段）类型
数据库中的每个表都是由一个或多个列（字段）构成的。在用CREATE TABLE语句创建一个表时，要为每列（字段）指定一个类型。列（字段）的类型比MySQL数据类型更为细化，它精确地描述了给定表列（字段）可能包含的值的种类，如是否带小数、是否文字很多。

4. 高性能MySQL：字符串类型（1）

字符串类型（ ）

MySQL 支持多种字符串类型 每种类型还有很多变种 这些数据类型在 和 版本发生了很大的变化 使得情况更加复杂 从MySQL 开始 每个字符串行可以定义自己的字符集和排序规则 或者说校对规则（collation）（更多关于这个主题的信息请参考第 章） 这些东西会很大程度上影响性能

VARCHAR 和CHAR 类型

VARCHAR 和CHAR 是两种最主要的字符串类型 不幸的是 很难精确地解释这些值是怎么存储在磁盘和内存中的 因为这跟存储引擎的具体实现有关 下面的描述假设使用的存储引擎是InnoDB 和/ 或者MyISAM 如果使用的不是这两种存储引擎 请参考所使用的存储引擎的文档

先看看VARCHAR 和CHAR 值通常在磁盘上怎么存储 请注意 存储引擎存储CHAR 或者VARCHAR 值的方式在内存中和在磁盘上可能不一样 所以MySQL 服务器从存储引擎读出的值可能需要转换为另一种存储格式 下面是关于两种类型的一些比较

VARCHAR

VARCHAR 类型用于存储可变长字符串 是最常见的字符串数据类型 它比定长类型更节省空间 因为它仅使用必要的空间（例如 越短的字符串使用越少的空间） 有一种情况例外 如果MySQL 表使用ROW_FORMAT=FIXED 创建的话 每一行都会使用定长存储 这会很浪费空间

VARCHAR 需要使用 或 个额外字节记录字符串的长度 如果列的最大长度小于或等于 字节 则只使用 个字节表示 否则使用 个字节 假设采用latin 字符集 一个VARCHAR（ ） 的列需要 个字节的存储空间 VARCHAR（ ） 的列则需要 个字节 因为需要 个字节存储长度信息

VARCHAR 节省了存储空间 所以对性能也有帮助 但是 由于行是变长的 在UPDATE 时可能使行变得比原来更长 这就导致需要做额外的工作 如果一个行占用的空间增长 并且在页内没有更多的空间可以存储 在这种情况下 不同的存储引擎的处理方式是不一样的 例如 MyISAM 会将行拆成不同的片段存储 InnoDB则需要分裂页来使行可以放进页内 其他一些存储引擎也许从不在原数据位置更新数据

下面这些情况下使用VARCHAR 是合适的 字符串行的最大长度比平均长度大很多 列的更新很少 所以碎片不是问题 使用了像UTF 这样复杂的字符集 每个字符都使用不同的字节数进行存储

在 或者更高版本 MySQL 在存储和检索时会保留末尾空格 但在 或更老的版本 MySQL 会剔除末尾空格

InnoDB 则更灵活 它可以把过长的VARCHAR 存储为BLOB 我们稍后讨论这个问题

CHAR

CHAR 类型是定长的 MySQL 总是根据定义的字符串长度分配足够的空间 当存储CHAR 值时 MySQL 会删除所有的末尾空格（在MySQL 和更老版本中VARCHAR也是这样实现的 也就是说这些版本中CHAR 和VARCHAR 在逻辑上是一样的 区别只是在存储格式上） CHAR 值会根据需要采用空格进行填充以方便比较

CHAR 适合存储很短的字符串 或者所有值都接近同一个长度 例如 CHAR 非常适合存储密码的MD 值 因为这是一个定长的值 对于经常变更的数据 CHAR 也比VARCHAR 更好 因为定长的CHAR 类型不容易产生碎片 对于非常短的列 CHAR 比VARCHAR 在存储空间上也更有效率 例如用CHAR（ ） 来存储只有Y 和N 的值 如果采用单字节字符集注 只需要一个字节 但是VARCHAR（ ） 却需要两个字节 因为还有一个记录长度的额外字节

CHAR 类型的这些行为可能有一点难以理解 下面通过一个具体的例子来说明 首先 我们创建一张只有一个CHAR（ ） 字段的表并且往里面插入一些值

当检索这些值的时候 会发现string 末尾的空格被截断了

如果用VARCHAR（ ） 字段存储相同的值 可以得到如下结果

数据如何存储取决于存储引擎 并非所有的存储引擎都会按照相同的方式处理定长和变长的字符串 Memory 引擎只支持定长的行 即使有变长字段也会根据最大长度分配最大空间 不过 填充和截取空格的行为在不同存储引擎都是一样的 因为这是在MySQL 服务器层进行处理的

返回目录 高性能MySQL

编辑推荐

ASP NET MVC 框架揭秘

Oracle索引技术

ASP NET开发培训视频教程

lishixin/Article/program/MySQL/201311/29687