易栈网-膘叔

字符串，恐怕应该算是MYSQL里面最复杂的类型了吧？几乎目前所有的问题，都是出在与字符有关的数据列上，大致有几种
1、字符串的查询（以下如果不特指，都是指中文），搜索一个中文的时候，不管是模糊还是精确，往往结果都会有与搜索内容不一致的数据在里面
2、编码，现在大家都知道MYSQL连接上后，先执行一下mysql_query('set names GBK',$conn)这类的语句，从MYSQL4.0升级到4.1及以上版本的朋友在这上面吃的苦不少了。网上关于这类的提问也是最多的
3、索引、效率，varchar是MYSQL所特有的字段，而且长度可变，char则是固定长度的字符串。
在MYSQL所支持的几个字符串格式里面，char和varchar是用的最多的，char是定长字段，，也就是说，不管字符串的实际长度有多少，CHAR(10)将永远占用10个字节。字符串如果前端有空格，那么在存储的时候会自动被数据库去掉，相当于先执行trim($string)，再进行存储，如果不满10个字节，将会采用空格填满，读取数据时，MYSQL会自动将这些空格去掉。看到这里，恐怕它的缺点之一就明显的暴露了，CHAR不能存储那些结尾确实有空格的字符串（因为读取时会被MYSQL自动去掉）。

数据类型	含义
char(n)	固定长度的字符串，最多255个字符
varchar(n)	可变长度字符串，最多255个字符（MYSQL<5.0.3,n<=255 , mysql>=5.0.3,n<=65535）
tinytext	可变长度字符串，最多255
text	可变长度字符串，最多65535-1
medinumtext	可变长度字符串，最多16,777,216-1
longtext	可变长度字符串，最多4,294,967,296-1

除了char类型外，其余类型均为可变长度，实际所占空间由该字段所存的内容决定。虽然varchar与tinytext一样都是255个字节，但不同的是varchar可以指定存储的长度，而tinytext只能由存储的时候数据长度而定。
在数据表被创建的时候，MYSQL 把数据列的定义改成一种对MYSQL来说更有效率的形式。这种自动方式的修改称为默许的数据列修改（client column changes）。一般来说是对CHAR和VARCHAR数据列都有影响：如果n<4，varchar(n)将被修改为char(n),而如果n>3并且在同一个数据表里还有其他的可变长度的字段，char将被修改成varchar格式。如果数据表里只有固定长度的数据列，char列将不发生变化。也就是说，你在数据表里的字段如果有多种格式，那么你通过alter table 是无法进行修改的。
上表提到，varchar在新版本里变成了65535个字节，但最大字符个数还是要取决于具体的字符集（GBK中文占两个字节，而UTF8中文则占三个字节）。上面这些所有的数据类型都有一个BINARY属性，一旦该字段添加了这个属性，那么MYSQL就会将他们视同BLOB字段来处理。BINARY的好处是在排序时把字符视为二进制，这样就可以把字母的大小写分开，而且由于是二进制字符串，所耗费的时间也会更短。可惜，在实陆应用中我们存储的大多是中文，所以这一点对我们来说并没有太大的区别。中文，本来就没有大小写之分，再怎么二进制，还是那样。呵呵。
在MYSQL4.0及以前的版本里，各种文本字段的字符集和排序方式都由MYSQL服务器决定，默认值是latin1字符集和瑞典排序方式。虽然允许在MYSQL配置文件里指定一种字符集和排序方式，但这会导致所有的数据库受到影响，还有，对字符集和排序方式做了任何修改都必须重启数据库服务器方能生效，更可恨的是，重启后，还必须重新创建所有的索引，同时，MYSQL4.0之前的版本并不支持UNICODE字符集。
4.1后，这些问题得到了很大的改状况，可以单独为某一个数据列指定一种字符集和排序方式，而且字符集和排序方式的选择范围也大了很多，因为从4.1开始支持了UNICODE和UCS-2等。与字符集及相关排序方式相关的命令是：SHOW COLLATION（必须要注意的是，字符集和排序方式不是随意组合的，每种字符集都有该字符集对应的一些排序方式，而不能混用）。如果没有为某个数据列指定一个字符集和排序方式，它将使用数据表、数据库或MYSQL数据库的默认字符集。至于数据库应该使用哪种字符类型，还是由你的项目所决定，如果你的项目采用了GBK编码，那数据库也同样使用这种编码比较好。如果你在项目初始时对于采用哪种编码未知，那么UTF8应该算是比较好的一种解决方法，数据迁移以及对于JAVA、.net等程序的支持也是最好的，唯一的缺点就是占用数据库空间太大。
说到现在，一直在说latin1和unicode字符集，这代表了什么意思呢？
latin1：在最初的MYSQL版本里都是默认采用了这种字符集，它是符合ISO-8859-1的规范的，收录了西欧国家的常用字符，在那个年代，国内还没有多少人使用MYSQL，而真正使用这些的大多是欧美国家，所以这个字符集主要就是用于收集这些国家常用的字符。随后latin的标准 在不停的变化，有latin2,latin9等，但它们当中并没有一个能够把所有欧洲语言里所有的字符都收录齐全，也就是说latin库里并没有一个可以适用于整个欧洲。
UNICODE：正由于latin不能解决这个问题，所以人们开发出了双字节的UNICODE字符集，两个字集意味着是2的16次方，可以存储65535个字符，所以UNICODE字符集不仅涵盖了所有的欧洲语言，还覆盖了绝大多数的亚洲语言。然而UNICODE只定义了每个字符的编码，没有定义如何存储这些编码。
在这个基础上，也同样发展出了好多变体。最重要的是UCS-2和UTF8，UCS-2又称UTF-16，即每种字符都用两个字节表示，所以几乎所有的字符都被涵盖了。但是这种也是有弊端的：存储空间大了一倍，即使是ASCII字符也是这样；字节编码0会出现在字符串中（比如ASCII写出来的文本里，每一个的第2个字节都是0），可是一些程序会把0当成结束标志，而导致出错。
UTF8是UTF16的替代品，在这里所有的ASCII（7位）仍然象以前一样用第一位是0的单字节表示，所有其他的UNICODE字符用2~4个字节表示。它的缺点也同样明显：同一份文档的字节数和字符数没有任何显而易见的关系，但由于它与现有的程序高度兼容，所以UTF8几乎已经成为了一种事实上的标准，而且大多数软件都把它作为自己的默认字符集。
可惜，PHP发展到现在对于UNICODE的支持还是一般性，并没有内置，处理的时候，不是用ICONV就是mb_string库（PHP6是说内置支持了），但并不能妨碍我们使用UTF8来开发网页。

字符串就介绍到这里。太累了。。。。。这个方面的问题太多了，在以后的连载中遇到再详细说明吧。

日期与时间型理论上应该是用的最多的，不管是什么时候，都需要使用时间，用户登录，文章编辑，甚至LOG，哪个不需要时间？然而在真正的应用中，却没有多少人在使用日期与时间型字段，大多数人都是使用int的时间戳来替代了日期型字段。那么，究竟应该怎样正确使用呢？日期型字段的优劣是什么？今天稍微介绍一下。
在MYSQL数据库中，关于日期和时间型字段有四种，date,time,datetime,year，还有一种比较特殊的timestamp字段。下面将一一介绍。

类型	含义
DATE	Y-m-d的时间格式，取值范围1000-01-01~9999-12-31，占用3个字节
TIME	H:i:s的时间格式，取值范围-838:59:59~+838:59:59，占用3个字节（说实话，我没有理解这个取值范围）
DATETIME	Y-m-d H:i:s的时间格式，是DATE+TIME的组合
YEAR	年份，取组范围1901~2155和0000，占用一个字节，YEAR有2位和4位之分，4位的就是前面的取值范围，2位的取值范围为70~69，代表了1970~2069
TIMESTAMP	虽然这个TIMESTAMP并非传统意义上的时间戳，但它的取值范围还是符合时间戳的标准，即从1970~2037，基本格式为：Y-m-d H:i:s，在以前的版本里，日期与时间之间并没有空格，如果为了兼容老的数据，在查询的时候则需要使用：select ts+0 from table来获取正确的时间

MYSQL在早期版本里对于时间类型的缺乏严格的判断，而只是仅仅判断数值是否在一个区间，却并不判断它是否合法：月份值允许0~12，日期值允许1~31，所有的数据验证都是由客户端判断好而数据库这边仅仅是一个存储作用。但这在5.0.2版本的时候，一切有了很大的改观，MYSQL开始对日期与时间型数据的判断非常严格，只有它认为合法的数据才存储到数据库，当然它认为0000-00-00仍然是合法数据。
不过，这仍然需要对MYSQL的系统变量sql_mode进行设置，大致有以下三个设置值：
1、ALLOW_INVALID_DATES    即不对数据有效性进行判断，仍然允许存入有明显错误的时间，比如：2001-02-31
2、NO_ZERO_DATE        在这种模式下，0000-00-00不再认为是合法日期
3、NO_ZERO_IN_DATE    不允许使用0作为月份值或者日期值
对于DATE,TIME,DATETIME,YEAR这四种格式，大家都可以从名字上能够看出来格式或者规范是什么样的，真正特殊的是TIMESTAMP格式。
对于TIMESTAMP格式，我想，用PHP开发的人应该是非常熟悉，时间戳嘛。在PHP里面直接使用time()函数，返回的就是当前时间的时间戳。可是，在MYSQL里，TIMESTAMP的存储值和PHP里的TIMESTAMP并不一样，就象上文说的，它存储的格式和DATETIME是一样的，只是它是自动存入的。
当你的表存在TIMESTAMP字段时，如果你对其他字段的内容进行修改，这个字段的值也会随之更新，几乎可以这么理解，这个字段就是MYSQL给你提供的数据记录的最后一次更新时间，正因为这个原因，所以TIMESTAMP字段大多数时候都是被用来管理和检查数据使用，而不会用来存储实际数据（因为一旦其他数据有更新，它就会自动更新）。
有一些需要与客户端程序或者函数进行交互的数据库操作，只有在数据表里存在一列TIMESTAMP字段才能正常工作，如Connector/ODBC，事实上就连MYSQL在对数据进行内部管理的时候，也需要知道它们的最后一次修改时间。虽然说该字段会被自动更新，但如果你在INSERT、UPDATE的时候，如果你指定了该字段的值，默认就会使用该值插入数据表。如果一个表里面有多个字段有TIMESTAMP属性，那么默认MYSQL是更新第一个有此属性的字段。
TIMESTAMP并没有想象中的那么不可控制，从mysql4.1.3开始，MYSQL为TIMESTAMP增加了两个属性，它们可以让用户更好的调控对TIMESTAMP列的刷新行为，使得这个字段可以更加好的为我们服务。

设置类型	含义
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	在创建新记录或者修改现有记录的时候对这个数据列进行刷新
TIMESTAMP	行为同上
TIMESTAMP DEFAULT CURRENT_TIMESTAMP	只在创建新记录的时候才刷新这个值，以后更新等操作时不会进行修改
TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	在创建新记录的时候设置值为0，在修改的时候才会更新它。
TIMESTAMP DEFAULT 'YYYY-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP	在创建新记录的时候设置值为指定值，以后在修改的时候刷新它。

如果偶尔遇到你在更新某条数据记录的时候，不想MYSQL自动刷新TIMESTAMP数据列，你只能按照下面的SQL格式来写：UPDATE table SET field = 'new value' , ts = ts;
有时候我们需要在时间列里面存入微秒，但目前而言，MYSQL还不能存储这样的结果。虽然这样的相关函数已经存在。
虽说MYSQL的日期格式往往都是'yyyy-mm-dd'这样的格式，但其实在insert和update的时候，还是可以接受'yyyy/mm/dd'这种符合日期规范格式的数据。所以，不要太担心。
对于日期处理，在SQL里面用date_format,format_unix等，这类函数太多了。如果以后有空我会慢慢介绍。

Heap数据表只存在于内存中，它主要的用途是充当临时数据表。它采用了散列索引（hash index），这使得数据的存取速度非常快。heap表与临时表还是有区别的，它对于访问数据库的连接是永远可见的，即使连接丢失，表也不会消失。
虽然它的存取速度是最快的，但缺点也同样的明显。
1、因为使用的是内存，所以一旦断电或出现意外的时候，里面的数据将全部丢失
2、因为使用内存，所以text字段和blob字段是不允许使用的
3、对于heap表，只能使用 = 和 <=> 操作符来进行记录的搜索，而不能使用 < , > ,<= ,>= 这些操作符
4、heap表不允许有自增字段
5、heap表的大小有限制，通过mysql配置文件中的：max_heap_table_size来决定

临时数据表，一般是指采用create temporary table或者mysql为了存储中间结果而临时创建的数据表，表的类型可以是myisam,innodb,heap三种类型中的任何一种。这种数据表在MYSQL意外掉电的时候，一般不会消失（除了heap表），但MYSQL正常关机、MYSQL连接结束、MYSQL执行意外中断时，都将全部丢失。另外，这种数据表对于访问同一个数据库的MYSQL连接是不可见的，不同的用户即使创建相同名字的临时表而不会发生冲突，这点就与heap有区别。因为heap表是可见的，所以不能创建两个相同名字的heap表。
一般而言，人们提到的“临时表”更多的是指MYSQL为了保存select查询的中间结果而自动创建的临时表。因为在实际运用当中，很少会主动创建这些临时表。
临时表的存储也和MYSQL默认的存储位置不一样，在windows下面，一般来说是在c:\windows\temp目录下，而在unix/linux环境里通常在/tmp或/var/tmp/或/usr/tmp目录下。这个子目录是可以在mysql启动时进行配置。

除了上面这些常用的数据表类型，mysql还支持一些其他数据表，但这需要自行编译时加入或者直接使用MAX版本的数据库。要想知道你的数据库支持哪些类型的数据表，你可以通过执行：show engines来查看。
MYSQL所支持的数据表还有：BDB表，ARCHIVE表（压缩数据表，从4.1开始支持），CSV表（4.1开始支持）,NDB表（mysql集簇，4.1开始支持），FEDERATED表（外部数据表，5.0开始支持）
下面简单的介绍一下，都是看来的，因为我都没有用过，黑黑
1、BDB表，这是最早具备事务功能的MYSQL数据表，但随着innodb驱动的日趋成熟，BDB已经几乎没有人使用了。
2、ARCHIVE表，和前面的提到的Mysql Compress表差不多，都是用于保存和备份数据而设计的。这种类型的表的优点是在保存数据之前会先对数据记录进行压缩。它只允许使用INSERTR命令，不允许执行UPDATE和DELETE命令。因为该表不允许数据被修改，同时，ARCHIVE表不支持索引，如果要进行SELECT操作，就必须遍历全部数据。因此，它只适合用于一些数据访问量很小的场合。
3、CSV表，这种类型大家应该很熟悉了，在使用excel的时候，就可以把数据存为CSV文件，一般采用逗号分割。基本上就是一文本文件，所以不支持索引功能。
4、NDB表。NDB集成在MYSQL MAX的版本中，是属于mysql集簇功能中的一种。NDB是英文network database的缩写，由名字也可以看出，它一般用来建设数据分布在大量计算机上的网络数据库，该表类型支持事务功能。如果需要使用该功能，必须在多台装有mysql max版本的联网计算机，并配置使他们都支持集簇功能时，才可以使用。
5、FEDERATED表。这种表的类型可以让用户去访问外部数据库里的数据表，而那个数据库系统可以位于本地网络中的另一台计算机上。在目前的MYSQL版本里，该功能所支持的外部数据库也必须是MYSQL，但未来，应该是可以支持更多版本。个人感觉，这就象一个proxy一样。在现有的版本里，通过FEDERATED进行的查询和事务都没有办法使用Query Cache工具优化，也不支持外部数据表的结构修改，但可以修改数据。即使用FEDERATED表时，不能使用alter table命令，但可以使用insert,update和delete命令。

最多的信息，也可以查看：http://dev.mysql.com/doc/mysql/en/storag-engines.html。

接下去就是介绍数据表文件和MYSQL所支持的一些数据类型了。

 

 从今天开始，逐步对MYSQL数据库的设计以及一些常规资料进行介绍。内容可能会参考网络或者书籍，当然还有自己的一些小小的经验。思维可能会有点跳跃，但可以肯定的是，本文没有一个字是从网上COPY下来，纯粹是手工输出。如果有错别字，请留言，我会尽快改正。 

 

数据库应用程序的第一阶段往往都是对数据库进行设计，设计的好坏对应用程序执行效率的高低、程序的编写以及后期的维护工作的难易程序和能否在今后灵活修改设计方案等问题会产生巨大且深远的影响。因此，可以这么认为，在设计阶段埋下的隐患会在后期给开发者和使用者带来无穷的烦恼和痛苦，而这个，却没有任何捷径可走，数据库设计方案的好坏与设计者的知识和经验是否丰富有着很大的关系，如果单位有DBA的话，或许要担心的事会少一点，如果没有，黑黑，那就等着受苦吧。

 

事先说明，本人的设计能力也非常差，所以，在设计一块，我是绝对参照书本而写，即使有个人见解也不会太多。

 

要设计数据库，就不得不了解MYSQL数据表的一些基本类型，我在以前的文章里也介绍过，只是今天在这里我会介绍的更详细一点。MYSQL支持多种数据表类型，比较重要和使用的多的表类型是：myisam,innodb,heap三种。

如果在创建数据库的时候，没有指定表的类型，一定是根据mysql的配置文件中：default-table-type这个选项来决定的，基本上，大多数的设定都是myisam，也有可能是innodb，但几乎可以肯定不会是heap,如果有，那实在是太妖了。

 

今天有点晚了，先介绍一下myisam类型的表的特点吧，明天再介绍其他的。

myisam的最大特点就是成熟、稳定和易于管理，这种类型也是mysql所特有的。并且，如果没有其他什么特殊应用，如事务等，一般建议是选用这种类型的表结构。

虽然大家都了解myisam，并且在实际应用中也正在使用这种类型的表结构，但恐怕你不知道的是，即使是myisam结构，也会为三种类型：

1、myisam static(静态myisam),一般来说，如果数据表的数据列各自都有预先定义好的固定长度，服务器会自动选用这种类型。该类型的存储、读取效率非常高，即使是大量的CRUD操作（只是为了方便，因为R是读取，删了R的话，又是一个新名词，所以还是把R放在了里面）也是如此。同时，该类型的表结构安全性很高，即使出现文件受损或其他问题，数据记录的提取和恢复工作也比其他类型的数据表更容易。

2、myisam dynamic(动态myisam)，这个就相对比较好解释了，凡是数据表结构里有varchar字段的，服务器就会自动选用这种类型。优点是：与静态myisam相比，本类型所占用的数据空间会小上很多，在存储字符串和二字制数据时所需要的字节数也往往就是它们的实际长度（或许会额外多出几个字节的开销）。缺点是，这样会造成每行数据的记录长度会不一致，如果数据在进行更新或删除操作的时候，很容易就会在原先存储这些数据的地方形成空洞，造成空间的浪费，而且还会造成数据的不连续，并分散在各处。当数据碎片越来越多的时候，数据的存取时间也就会越来越长。因此，就有了一个单独的方法：optimize table或者其他专门的工具来对数据表进行优化

3、myisam compressed(压缩型myisam)，以上两种类型都可以使用myisamchk工具进行压缩，一般来说，压缩后的数据库占用空间大多仅为原来大小的一半（当然这也于数据库存储的内容有关）。虽然以后在读取他们的时候需要先进行解压缩操作，但在“低速硬盘+高速CPU”的系统上，数据表的访问会变得极快。然而，这种类型的表有一个非常致命的缺点，即它们是只读的，不能进行任何写操作。

 

今天先介绍到这里，明天继续往下讲。希望会给大家带来帮助。