易栈网-膘叔

日期与时间型理论上应该是用的最多的，不管是什么时候，都需要使用时间，用户登录，文章编辑，甚至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等，这类函数太多了。如果以后有空我会慢慢介绍。

今天我们继续学习一下MYSQL的数据类型
浮点型
从MYSQL3.23开始，FLOAT和DOUBLE类型就一直分别对应着IEEE标准所定义的单精度浮点数和双精度浮点数，绝大多数编程语言也都支持这两种数据类型。
当某个字段设置成这两种类型时，会要求按(m,d)这样的格式输入参数，其中m和上文介绍INT时所讲的m效果一样，仅仅在显示/打印结果时有一定的排版作用，而对数据的精确度没有影响。可是d这个参数就不一样了,d代表了小数点后面的数字个数，而且会按照d的大小来进行四舍五入，例如：1234.5678如果存储到FLOAT(8,3)这样的字段里，实际存放的是1234.568，最后一位会四舍五入。
存储FLOAT和DOUBLE时，如果超出了这两种类型的范围，将会被替换成该类型的最大可取值（一般由当前操作系统决定，我不知道MYSQL是怎么样。），可以使用SHOW WARNING来查看警告内容。
MYSQL对浮点数有一定的要求，即一定要符合通用计数法，小数点一定是“.”，而不能是“,”（因为有部分欧洲国家使用逗号作为小数点，我也是第一次听说，黑黑），MYSQL永远会以这种写法向客户返回查询结果，只有在非常大或非常小的数值，会采用科学计数法表示，如1.234E+017。如果想让浮点数表现形式变成其他样式，有两种方式：一是利用SQL查询中的FORMAT函数，二是用客户端程序中的FORMAT函数，例如php中就可以使用sprintf或者number_format等来改变显示效果

类型	含义
FLOAT(m,d)	单精度浮点数，8位精度，占4字节
DOUBLE(m,d)	双精度浮点数，16位精度，占8字节
REAL(m,d)	DOUBLE的同义词

说到浮点数，当然要提定点数了
正因为MYSQL在处理浮点数时，最后一位会被自动的四舍五入，而我们实际上并不需要这一位被四舍五入（比如在处理财务数据时），怎么办呢？定点数可以解决这个问题。
定点数（DECIMAL）数字是以字符串的形式来进行存储的，并且不被允许存储指数形式，每位数字占用一个字节，外加两个字节的开销，所以，占用了更多的空间，但可表示的数值范围却比较小。
在进行存储的时候，也有两个参数DECIMAL(p,s)这回，这两个参数都有作用了。p代表了数据的总位数（不含小数点），最大65，s代表了小数点后的数字位数，最大为30。比方说：DECIMAL(6,3)取值范围为-999.999~999.999，这是新的版本的表示方法。不知道我有没有记错，我记得在以前的版本里，DECIMAL(6,3)，这个6并不是代表了总位数，而是小数点前的位数（可能是在4.0吧）。
在MYSQL内部，定点数是被保存为二进制格式的，把定点数从小数点开始分割，并为它们各自分配所占用的字节数（最初是以每4个字节进行划分，虽然每个字节可以容纳2位数字，但4个字节却可以容纳9位数字，超过4个字节后，如果有多出的数字就划分一个字节来进行存储，同样如果是9位数字，还是只占4个字节）。根据这个规定，我们可以认为DECIMAL(6,3)其实是和DECIMAL(18,9)占用了同样多的字节数（8个字节）。如此说来DECIMAL(19,9)实际就占用了9个字节。

类型	含义
DECIMAL(p,s)	定点数，以字符串形式保存，最长为65
NUMERIC,DEC	同上（应该没有记错吧）

参考：http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

介绍完表的结构和存放位置后，下面的内容就是介绍MYSQL的数据类型了，毕竟为你想要的数据选定一个合适的类型，对于数据库的存取效率有很大的改进。
MYSQL数据库有以下几种类型：整数型(int,binint,smallint,tinyint等等xxxint型)、浮点型(float,double,real等)、定点数型(decimal，也就是传说中的浮点型的字符串表达形式)、日期与时间型（data,time,datetime,timestamp），字符串型（char,varchar,text,tinytext,mediumtext,longtext等），二进制型（tinyblob,tinyblob,blob等blob型），枚举型(enum,set——set是类似于enum型)。

下面从整数型开始详细介绍这些数型的基本信息：
整数型
默认情况下，INT类型既包括正数，也包括负数，如果给INT列定义UNSIGNED属性，那么它的取值范围就永远是正数。这个永远是正数，非常重要，即，如果你在对数据列操作：update tablename set int_field = (int_field - 1000 )，如果int_field的值小于1000，理论上是变成了负数，但由于你设的属性是UNSIGNED，那么存储的数据仍然是无符号的正数，这可能会给你带来虚假或让人困惑的结果。
TINYINT的取值范围是从-128~+127，如果使用了UNSIGNED属性，那么它的取值范围就变成了0~+255。如果用户试图存入超过字段取值范围的数字，MYSQL所做的工作就非常简单，它会直接替换成最大可取值或最小可取值。
或许有人注意到，我们在使用phpmyadmin的时候，如果选择INT类型，那么后面有一个可选长度。不过，千万不要被这个数值所迷惑，因为它的实际功能并非指定该INT类型的长度，而是最大显示宽度（M：Maximum Display Width），仅仅用于查询数据时可以把查询结果按照你所指定的宽度进行显示，最主要的用途是为了排版需要。虽说是这样，但在一些很少见的特定场合（MYSQL在执行一些需要借助于临时数据表才能完成的复杂查询）里，临时数据表的数值有可能会被截短并导致最终结果不正确。因此，除非你与数据库所有的操作都在命令行下执行，否则，还是建议留空。

INT 类型	该类型的实际含义
Tinyint(m)	8位整数，占用1个字节（-128~+127）
smallint(m)	16位整数，占用2个字节（-32768~+32767）
medimumint(m)	24位整数，占用3个字节（-8388608~+8388607）
int(m),integer(m)	32位整数，占用4个字节（-2147483648~+2147483647）
bigint(m)	64位整数，占用8个字节（-9.22E+18 ~ +9.22E+18）
serial	这其实是bigint auto_increment not null primary key的简写

上面tinyint(m)括号中的m，就是上文所指的最大显示宽度。最后一个serial含义里的auto_increment，代表了该字段是自增字段，即，往有该属性的字段里插数据时，系统会自动把该字段的当前最大值加上1后存进去。 一般而言，该属性往往用于主键字段。在使用该属性时应当注意以下几个问题。
1、该属性必须与NOT NULL、primary Key 或者 Unique属性同时使用。
2、每个数据表只能存有一个auto_increment属性的数据列
3、该属性一般是在进行数据插入时，没有明确指定值或者指定值为NULL时，才起作用。如果指定了值，并且该值还没有出现过，MYSQL将使用该值插入，以生成一条新记录。这时候就有两种情况：a)原有的数据是1~100，后来删除了20~80的内容，即数据库里只存在1~19,81~100的数据，当插入ID为20的数据时，数据库还是会按照规则，把20这条记录插进去，并不会报错，自增值仍然是100，下次正常插入时，还是会默认使用101这个值。b)原有的数据是1~100，我插入ID为1000的数据时，不会报错，但自增值变为1000，下次插入数据从1001起算。（啰嗦了点，但应该记住）
4、如果想知道刚刚插入的数据值是多少，在插入数据后，可以使用select last_insert_id()语句获取。在PHP中，有一个函数是:mysql_insert_id()，该函数不是很建议使用，因为它返回的数据是INT型，如果auto_increment所在的数据列类型是bigint，而且实际值已经超过int类型最大值的话，mysql_insert_id()返回的值，将不正确。
5、如果auto_increment计数器达到了最大值（即该字段所允许的最大值），将不再递增，因此数据插入将无法执行。其实是到了最大值后，MYSQL会永远将该字段的最大值往数据库里插，因此造成MYSQL报：该数据列已存在的错误。
6、如果在可行范围内，或者预计数量会很大的情况下，尽量使用你想要设定的数据类型的再上一层类型，但尽量不要太夸张。比如你预计你的用户数不会超过1000000万，表面上medimum无符号已经够用了，但这时候，仍然建议你使用int型，以防万一。

打字很累，剩下的留到以后慢慢讲。。。

差点忘了。。。还有个BIT和BOOL，在MYSQL中，关键词BOOL是TINYINT的同义词，在5.0.2以及以前的版本中，BIT也是如此。但是从5.0.3开始，BIT不再是TINYINT的同义词，而是一种可以存储多达64位二进制数值的新数据类型。这个在以后会单独介绍，这里一笔带过先。

明天，就是俺结婚2周年的纪念日了，为了感谢大家的支持，所以。今天再开放一篇连载。黑黑

介绍完MYSQL所支持的一些数据表类型，再介绍一下数据表文件的存放等资料。

MYSQL对于数据表的存放一般而言都是很有规律的，在*nix系统下面，通常在/var/lib/mysql子目录，而在windows平台下一般都在mysql安装目录下的子目录/data/里。

（写了一半，发现都需要用到数据存放目录，现在假设数据存放目录为/data/）

一般而言，每一个数据表都有一个和表名对应的frm文件，而这些数据表都是存储在以它本身所在数据库为名的目录里。例如：User数据库下的members表，一般都存储在数据库目录下的/User/下，名字为members.frm（全路径为：/data/User/members.frm）。frm文件是整个数据库的结构定义和设置。

如果是myisam数据表，那么还会创建两个以数据表命名的文件，*.MYD,*.MYI，其中MYD文件用来存放MYISAM数据表的数据，而MYI文件用来存放索引（该表的所有索引）。

如果是innoDB数据表的话，就需要根据MYSQL配置文件中的innodb_file_per_table的选项设置情况而定。它既可以各自存为一个文件，也可以统一存放在所谓的表空间(tablespace)里。表空间的存放位置和名字由配置决定。如果没有做任何设置的话，默认是innodb数据表的数据和索引存放在/data/dbname/tablename.idb文件里，而把表空间和撤销日志（undo log）存放在/data/ibdata1,/data/ibdata2/...等文件里，而把innodb的日志数据存放在/data/ib_logfile0 ,/data/ib_logfile1这样有规则的文件里。

在如今的版本里，如果你定义了触发器，那么它们存放在/data/databasename/tablename.TRG文件里。新版本据说可能会发生变化。

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所支持的一些数据类型了。