2016-04-23

数据库

数据库范式详解

2016-04-23

数据库

MySQL数据类型

MySQL数据类型，可以被分为3类：数值类型、日期和时间类型以及字符串(字符)类型

数据类型的修饰符：

方括号(“[”和“]”)指出可选的类型修饰符的部分
M ：指出最大的显示尺寸。最大的合法的显示尺寸是 255 。
D ：适用于浮点类型并且指出跟随在十进制小数点后的数码的数量。最大可能的值是30，但是应该不大于M-2。
ZEROFILL ：填零，即在数字长度不够的数据前面填充0，以达到设定的长度，MySQL中字段设置为该属性时，将为该列自动地增加UNSIGNED属性。
UNSIGNED ：为“无符号”的意思, 即为非负数。是MYSQL自定义的类型，非标准SQL。

unsigned 属性只针对整型,用途：

UNSIGNED 可用来约束数据的范围，例如有些年龄这种值一般能是负数，那么就可以设置一个 UNSIGNED ，这样可以不允许负数插入。

可以增加数值范围（相当于把负数那部分加到正数上）。不过少用，不方便移植。

一.数值类型

整型

在MySQL中创建整型字段INT（或其它像tinyint）时，可以设定该字段的位数。如int(11),int(5),如果不指定位数，INT型默认长度为11。字段插入长度与int设定的M无关，当插入长度大于设定的M时，字段值不会被截断，还是按照类型的实际精度进行保存。

整型字段有个ZEROFILL属性，在数字长度不够的数据前面填充0，以达到设定的长度。M值只当属性为ZEROFILL时，才有区别效果。

如表结构为id1 int(10) ，id2 int(5),对应数据为第一行为（1,1），第二行为（1111111,1111111），当id1，id2字段设置为ZEROFILL属性时，显示数据为第一行为（0000000001,00001），第二行为（0001111111,1111111），不够的位数会以0补齐。

数据类型	大小	M	范围（有符号）	范围（无符号）	用途
tinyint [(M)] [UNSIGNED] [ZEROFILL]	1字节	4	-128~127 (-2^7 ~ 2^7 -1)	0 ~ 255 (0 ~ 2^8 -1)	非常小整数值
smallint [(M)] [UNSIGNED] [ZEROFILL]	2字节	6	-32768~32767（-2^15 ~ 2^15 -1)	0 ~ 65535 (0 ~ 2^16 -1)	较小整数
mediumint [(M)] [UNSIGNED] [ZEROFILL]	3字节	9	-8388608 ~ 8388607 （-2^23 ~ 2^23 -1）	0 ~ 16777215 (0 ~ 2^24 -1)	中等大小整数
int [(M)] [UNSIGNED] [ZEROFILL]	4字节	11	-2147483648 ~ 2147483647（-2^31 ~ 2^31 -1）	0 ~ 4294967295 (0 ~ 2^32 -1)	标准整数
integer [(M)] [UNSIGNED] [ZEROFILL]	4字节	11	-2147483648 ~ 2147483647（-2^31 ~ 2^31 -1）	0 ~ 4294967295 (0 ~ 2^32 -1)	和int相同
bigint [(M)] [UNSIGNED] [ZEROFILL]	8字节	20	-9223372036854775808 ~9223372036854775807(-2^63 ~ 2^63 -1）	0 ~ 18446744073709551615(0 ~ 2^64 -1)	较大整数

浮点型

对每种浮点类型，可指定一个最大的显示尺寸M和小数位数D。M的值应该取1到255。D的值可为0到30，但是不应大于M-2。M和D对float和double都是可选的，但对于decimal是必须的，在选项M和D时，如果省略了它们，则使用缺省值，如果D被省略，它被设置为0。如果M被省掉，它被设置为10。

数据类型	大小	用途
float [(M,D)] [ZEROFILL]	4字节	单精度浮点型，8位精度；参数m只影响显示效果，不影响精度，d却不同，会影响到精度；m是十进制数字的总个数，d是小数点后面的数字个数
double[(M,D)] [ZEROFILL]	8字节	双精度浮点型，16位精度；参数m只影响显示效果，不影响精度，d却不同，会影响到精度
real[(M,D)] [ZEROFILL]	8字节	同double
decimal[(M[,D])] [ZEROFILL]	4字节	decimal(m,d) 定点类型浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。参数m是定点类型数字的最大个数（精度），范围为0~65，d小数点右侧数字的个数，范围为0~30，但不得超过m。对定点数的计算能精确到65位数字。DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位
numeric[(M,D)] [ZEROFILL]	4字节	同decimal

(M,D) 表示总共M位，D个小数位，D包含于M中。浮点类型不能是unsigned的。

DECIMAL 类型不同于FLOAT和DECIMAL，其中DECIMAL 实际是以串存放的。DECIMAL 可能的最大取值范围与DOUBLE 一样，但是其有效的取值范围由M 和D 的值决定。如果改变M 而固定D，则其取值范围将随M 的变大而变大。表2 - 7的前三行说明了这一点。如果固定M 而改变D，则其取值范围将随D 的变大而变小（但精度增加）。表2 - 7的后三行说明了这一点。
1629270

给定的DECIMAL 类型的取值范围取决于MySQL数据类型的版本。对于MySQL3.23 以前的版本，DECIMAL(M, D) 列的每个值占用M 字节，而符号（如果需要）和小数点包括在M 字节中。因此，类型为DECIMAL(5, 2) 的列，其取值范围为-9.99 到9 9 . 9 9，因为它们覆盖了所有可能的5 个字符的值。正如MySQL3.23 一样，DECIMAL 值是根据ANSI 规范进行处理的， ANSI 规范规定DECIMAL(M, D) 必须能够表示M 位数字及D 位小数的任何值。

例如， DECIMAL(5, 2) 必须能够表示从-999.99 到999.99 的所有值。而且必须存储符号和小数点，因此自MySQL3.23以来DECIMAL 值占M + 2 个字节。对于DECIMAL(5, 2)，“最长”的值（- 9 9 9 . 9 9）需要7个字节。在正取值范围的一端，不需要正号，因此MySQL数据类型利用它扩充了取值范围，使其超过了ANSI 所规范所要求的取值范围。如DECIMAL(5, 2) 的最大值为9 9 9 9 . 9 9，因为有7 个字节可用。

简而言之，在MySQL3.23 及以后的版本中，DECIMAL(M, D) 的取值范围等于更早版本中的DECIMAL(M + 2, D) 的取值范围。在MySQL数据类型的所有版本中，如果某个DECIMAL 列的D 为0，则不存储小数点。这样做的结果是扩充了列的取值范围，因为过去用来存储小数点的字节现在可用来存放其他数字了。

二.字符类型

对于可变长的字符类型，其值所占的存储量是不同的，这取决于实际存放在列中的值的长度，这个长度用L表示。

数据类型	大小（范围）	用途
char(M)[BINARY]	M个字节，0 <= M <= 255 (L为固定的=255，不够补空格)	定长字符串；CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。CHAR值根据缺省字符集以大小写不区分的方式排序和比较，除非给出BINARY关键词
varchar(M) [BINARY]	L+1个字节，其中L <= M 且0 <= M <= 65535（MySQL5.0之前都是最大255)	变长字符串；VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同
tinyblob，tinytext	L+1个字节，其中L < 28-1 (255)	tinyblob：不超过 255 个字符的二进制字符串；tinytext：短文本字符串
blob,text	L+2个字节，其中L < 216-1(65535)	blob：二进制形式的长文本数据，在分类和比较时BLOB 类型区分大小写;text：长文本数据，在分类和比较时TEXT 不区分大小写
mediumblob,mediumtext	L+3个字节，其中L < 224-1	mediumblob：二进制形式的中等长度文本数据；mediumtext ：中等长度文本数据，
longblob,longtext	L+4个字节，其中L < 232-1	longblob：二进制形式的极大文本数据；longtext ：极大文本数据
enum(‘value1’,’value2’,…)	1或2个字节，取决于枚举值的个数(最多65,535个值)
set(‘value1’,’value2’,…)	1、2、3、4或者8个字节，取决于set成员的数目(最多64个成员)

三.日期类型

MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。

类型	大小	范围	格式	用途
YEAR[(2	4)]	1字节	1901/2155	YYYY	年份值
DATE	3字节	‘1000-01-01’–’9999-12-31’	YYYY-MM-DD	日期值
TIME	3字节	‘-838:59:59’到’838:59:59’	HH:MM:SS	时间值或持续时间
DATETIME	8字节	‘1000-01-01 00:00:00’–’9999-12-31 23:59:59’	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP[(M)]	8字节	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

YEAR

给YEAR类型赋值可以有三种方法。

第一种是直接插入4位字符串或者4位数字。
第二种是插入2位字符串，这种情况下如果插入‘00’~‘69’，则相当于插入2000~2069；如果插入‘70’~‘99’，则相当于插入1970~1999。第二种情况下插入的如果是‘0’，则与插入‘00’效果相同，都是表示2000年。
第三种是插入2位数字，它与第二种（插入两位字符串）不同之处仅在于：如果插入的是一位数字0，则表示的是0000，而不是2000年。所以在给YEAR类型赋值时，一定要分清0和‘0’，虽然两者相差个引号，但实际效果确实相差了2000年。

DATE

MySQL是以YYYY-MM-DD格式来显示DATE类型的值，插入数据时，数据可以保持这种格式。另外，MySQL还支持一些不严格的语法格式，分隔符“-”可以用“@”、“.”等众多富豪来替代。在插入数据时，也可以使用“YY-MM-DD”格式，YY转化成对应的年份的规则与YEAR类型类似。如果我们想插入当前系统的时间，则可以插入CURRENT_DATE或者NOW()。

允许使用字符串或数字把值赋给DATE列。

TIME

TIME类型表示为“时：分：秒”，尽管小时范围一般是0~23，但是为了表示某些特殊时间间隔，MySQL将TIME的小时范围扩发了，而且支持负值。对TIME类型赋值，标准格式是”HH：MM：SS”，但不一定非要是这种格式。如果插入的是”D HH：MM：SS”格式，则类似插入了”（D*24+HH）：MM：SS”。比如插入”2 23:50:50”，相当于插入了”71:50:50”。如果插入的是”HH：MM”或”SS”格式，则效果是其他未被表示位的值赋为零值。比如插入”30”，相当于插入了”00:00:30”；如果插入”11:25”，相当于插入了”11:25:00”。

另外也可以插入‘D HH’和‘D HH：MM’，效果按上面的例子可以推理出来了吧。在MySQl中，对于’HHMMSS’格式，系统能够自动转化为标准格式。如果我们想插入当前系统的时间，则可以插入CURRENT_TIME或者NOW()。

TIME类型允许使用字符串或数字把值赋给TIME列,只占3个字节，如果只是存储时间数据，它最合适了。

需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。

DATETIME

通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

TIMESTAMP

通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

TIMESTAMP的取值范围比较小，没有DATETIME的取值范围大，因此输入值时一定要保证在TIMESTAMP的范围之内。它的插入也与插入其他日期和时间数据类型类似。

那么TIMESTAMP类型如何插入当前时间？第一，可以使用CURRENT_TIMESTAMP；第二，输入NULL，系统自动输入当前的TIMESTAMP；第三，无任何输入，系统自动输入当前的TIMESTAMP。

另外有很特殊的一点：TIMESTAMP的数值是与时区相关.

MySQL以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD格式来显示TIMESTAMP值，取决于是否M是14（或省略)、12、8或6，但是允许你使用字符串或数字把值赋给TIMESTAMP列。一个TIMESTAMP列对于记录一个INSERT或UPDATE操作的日期和时间是有用的，因为如果你不自己给它赋值，它自动地被设置为最近操作的日期和时间。你以可以通过赋给它一个NULL值设置它为当前的日期和时间

2016-04-23

数据库

MySQL数据类型

MySQL数据类型，可以被分为3类：数值类型、日期和时间类型以及字符串(字符)类型

数据类型的修饰符：

方括号(“[”和“]”)指出可选的类型修饰符的部分
M ：指出最大的显示尺寸。最大的合法的显示尺寸是 255 。
D ：适用于浮点类型并且指出跟随在十进制小数点后的数码的数量。最大可能的值是30，但是应该不大于M-2。
ZEROFILL ：填零，即在数字长度不够的数据前面填充0，以达到设定的长度，MySQL中字段设置为该属性时，将为该列自动地增加UNSIGNED属性。
UNSIGNED ：为“无符号”的意思, 即为非负数。是MYSQL自定义的类型，非标准SQL。

unsigned 属性只针对整型,用途：

UNSIGNED 可用来约束数据的范围，例如有些年龄这种值一般能是负数，那么就可以设置一个 UNSIGNED ，这样可以不允许负数插入。

可以增加数值范围（相当于把负数那部分加到正数上）。不过少用，不方便移植。

一.数值类型

整型

在MySQL中创建整型字段INT（或其它像tinyint）时，可以设定该字段的位数。如int(11),int(5),如果不指定位数，INT型默认长度为11。字段插入长度与int设定的M无关，当插入长度大于设定的M时，字段值不会被截断，还是按照类型的实际精度进行保存。

整型字段有个ZEROFILL属性，在数字长度不够的数据前面填充0，以达到设定的长度。M值只当属性为ZEROFILL时，才有区别效果。

如表结构为id1 int(10) ，id2 int(5),对应数据为第一行为（1,1），第二行为（1111111,1111111），当id1，id2字段设置为ZEROFILL属性时，显示数据为第一行为（0000000001,00001），第二行为（0001111111,1111111），不够的位数会以0补齐。

数据类型	大小	M	范围（有符号）	范围（无符号）	用途
tinyint [(M)] [UNSIGNED] [ZEROFILL]	1字节	4	-128~127 (-2^7 ~ 2^7 -1)	0 ~ 255 (0 ~ 2^8 -1)	非常小整数值
smallint [(M)] [UNSIGNED] [ZEROFILL]	2字节	6	-32768~32767（-2^15 ~ 2^15 -1)	0 ~ 65535 (0 ~ 2^16 -1)	较小整数
mediumint [(M)] [UNSIGNED] [ZEROFILL]	3字节	9	-8388608 ~ 8388607 （-2^23 ~ 2^23 -1）	0 ~ 16777215 (0 ~ 2^24 -1)	中等大小整数
int [(M)] [UNSIGNED] [ZEROFILL]	4字节	11	-2147483648 ~ 2147483647（-2^31 ~ 2^31 -1）	0 ~ 4294967295 (0 ~ 2^32 -1)	标准整数
integer [(M)] [UNSIGNED] [ZEROFILL]	4字节	11	-2147483648 ~ 2147483647（-2^31 ~ 2^31 -1）	0 ~ 4294967295 (0 ~ 2^32 -1)	和int相同
bigint [(M)] [UNSIGNED] [ZEROFILL]	8字节	20	-9223372036854775808 ~9223372036854775807(-2^63 ~ 2^63 -1）	0 ~ 18446744073709551615(0 ~ 2^64 -1)	较大整数

浮点型

对每种浮点类型，可指定一个最大的显示尺寸M和小数位数D。M的值应该取1到255。D的值可为0到30，但是不应大于M-2。M和D对float和double都是可选的，但对于decimal是必须的，在选项M和D时，如果省略了它们，则使用缺省值，如果D被省略，它被设置为0。如果M被省掉，它被设置为10。

数据类型	大小	用途
float [(M,D)] [ZEROFILL]	4字节	单精度浮点型，8位精度；参数m只影响显示效果，不影响精度，d却不同，会影响到精度；m是十进制数字的总个数，d是小数点后面的数字个数
double[(M,D)] [ZEROFILL]	8字节	双精度浮点型，16位精度；参数m只影响显示效果，不影响精度，d却不同，会影响到精度
real[(M,D)] [ZEROFILL]	8字节	同double
decimal[(M[,D])] [ZEROFILL]	4字节	decimal(m,d) 定点类型浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。参数m是定点类型数字的最大个数（精度），范围为0~65，d小数点右侧数字的个数，范围为0~30，但不得超过m。对定点数的计算能精确到65位数字。DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位
numeric[(M,D)] [ZEROFILL]	4字节	同decimal

(M,D) 表示总共M位，D个小数位，D包含于M中。浮点类型不能是unsigned的。

DECIMAL 类型不同于FLOAT和DECIMAL，其中DECIMAL 实际是以串存放的。DECIMAL 可能的最大取值范围与DOUBLE 一样，但是其有效的取值范围由M 和D 的值决定。如果改变M 而固定D，则其取值范围将随M 的变大而变大。表2 - 7的前三行说明了这一点。如果固定M 而改变D，则其取值范围将随D 的变大而变小（但精度增加）。表2 - 7的后三行说明了这一点。
1629270

给定的DECIMAL 类型的取值范围取决于MySQL数据类型的版本。对于MySQL3.23 以前的版本，DECIMAL(M, D) 列的每个值占用M 字节，而符号（如果需要）和小数点包括在M 字节中。因此，类型为DECIMAL(5, 2) 的列，其取值范围为-9.99 到9 9 . 9 9，因为它们覆盖了所有可能的5 个字符的值。正如MySQL3.23 一样，DECIMAL 值是根据ANSI 规范进行处理的， ANSI 规范规定DECIMAL(M, D) 必须能够表示M 位数字及D 位小数的任何值。

例如， DECIMAL(5, 2) 必须能够表示从-999.99 到999.99 的所有值。而且必须存储符号和小数点，因此自MySQL3.23以来DECIMAL 值占M + 2 个字节。对于DECIMAL(5, 2)，“最长”的值（- 9 9 9 . 9 9）需要7个字节。在正取值范围的一端，不需要正号，因此MySQL数据类型利用它扩充了取值范围，使其超过了ANSI 所规范所要求的取值范围。如DECIMAL(5, 2) 的最大值为9 9 9 9 . 9 9，因为有7 个字节可用。

简而言之，在MySQL3.23 及以后的版本中，DECIMAL(M, D) 的取值范围等于更早版本中的DECIMAL(M + 2, D) 的取值范围。在MySQL数据类型的所有版本中，如果某个DECIMAL 列的D 为0，则不存储小数点。这样做的结果是扩充了列的取值范围，因为过去用来存储小数点的字节现在可用来存放其他数字了。

二.字符类型

对于可变长的字符类型，其值所占的存储量是不同的，这取决于实际存放在列中的值的长度，这个长度用L表示。

数据类型	大小（范围）	用途
char(M)[BINARY]	M个字节，0 <= M <= 255 (L为固定的=255，不够补空格)	定长字符串；CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。CHAR值根据缺省字符集以大小写不区分的方式排序和比较，除非给出BINARY关键词
varchar(M) [BINARY]	L+1个字节，其中L <= M 且0 <= M <= 65535（MySQL5.0之前都是最大255)	变长字符串；VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同
tinyblob，tinytext	L+1个字节，其中L < 28-1 (255)	tinyblob：不超过 255 个字符的二进制字符串；tinytext：短文本字符串
blob,text	L+2个字节，其中L < 216-1(65535)	blob：二进制形式的长文本数据，在分类和比较时BLOB 类型区分大小写;text：长文本数据，在分类和比较时TEXT 不区分大小写
mediumblob,mediumtext	L+3个字节，其中L < 224-1	mediumblob：二进制形式的中等长度文本数据；mediumtext ：中等长度文本数据，
longblob,longtext	L+4个字节，其中L < 232-1	longblob：二进制形式的极大文本数据；longtext ：极大文本数据
enum(‘value1’,’value2’,…)	1或2个字节，取决于枚举值的个数(最多65,535个值)
set(‘value1’,’value2’,…)	1、2、3、4或者8个字节，取决于set成员的数目(最多64个成员)

三.日期类型

MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。

类型	大小	范围	格式	用途
YEAR[(2	4)]	1字节	1901/2155	YYYY	年份值
DATE	3字节	‘1000-01-01’–’9999-12-31’	YYYY-MM-DD	日期值
TIME	3字节	‘-838:59:59’到’838:59:59’	HH:MM:SS	时间值或持续时间
DATETIME	8字节	‘1000-01-01 00:00:00’–’9999-12-31 23:59:59’	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP[(M)]	8字节	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

YEAR

给YEAR类型赋值可以有三种方法。

第一种是直接插入4位字符串或者4位数字。
第二种是插入2位字符串，这种情况下如果插入‘00’~‘69’，则相当于插入2000~2069；如果插入‘70’~‘99’，则相当于插入1970~1999。第二种情况下插入的如果是‘0’，则与插入‘00’效果相同，都是表示2000年。
第三种是插入2位数字，它与第二种（插入两位字符串）不同之处仅在于：如果插入的是一位数字0，则表示的是0000，而不是2000年。所以在给YEAR类型赋值时，一定要分清0和‘0’，虽然两者相差个引号，但实际效果确实相差了2000年。

DATE

MySQL是以YYYY-MM-DD格式来显示DATE类型的值，插入数据时，数据可以保持这种格式。另外，MySQL还支持一些不严格的语法格式，分隔符“-”可以用“@”、“.”等众多富豪来替代。在插入数据时，也可以使用“YY-MM-DD”格式，YY转化成对应的年份的规则与YEAR类型类似。如果我们想插入当前系统的时间，则可以插入CURRENT_DATE或者NOW()。

允许使用字符串或数字把值赋给DATE列。

TIME

TIME类型表示为“时：分：秒”，尽管小时范围一般是0~23，但是为了表示某些特殊时间间隔，MySQL将TIME的小时范围扩发了，而且支持负值。对TIME类型赋值，标准格式是”HH：MM：SS”，但不一定非要是这种格式。如果插入的是”D HH：MM：SS”格式，则类似插入了”（D*24+HH）：MM：SS”。比如插入”2 23:50:50”，相当于插入了”71:50:50”。如果插入的是”HH：MM”或”SS”格式，则效果是其他未被表示位的值赋为零值。比如插入”30”，相当于插入了”00:00:30”；如果插入”11:25”，相当于插入了”11:25:00”。

另外也可以插入‘D HH’和‘D HH：MM’，效果按上面的例子可以推理出来了吧。在MySQl中，对于’HHMMSS’格式，系统能够自动转化为标准格式。如果我们想插入当前系统的时间，则可以插入CURRENT_TIME或者NOW()。

TIME类型允许使用字符串或数字把值赋给TIME列,只占3个字节，如果只是存储时间数据，它最合适了。

需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。

DATETIME

通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

TIMESTAMP

通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

TIMESTAMP的取值范围比较小，没有DATETIME的取值范围大，因此输入值时一定要保证在TIMESTAMP的范围之内。它的插入也与插入其他日期和时间数据类型类似。

那么TIMESTAMP类型如何插入当前时间？第一，可以使用CURRENT_TIMESTAMP；第二，输入NULL，系统自动输入当前的TIMESTAMP；第三，无任何输入，系统自动输入当前的TIMESTAMP。

另外有很特殊的一点：TIMESTAMP的数值是与时区相关.

MySQL以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD格式来显示TIMESTAMP值，取决于是否M是14（或省略)、12、8或6，但是允许你使用字符串或数字把值赋给TIMESTAMP列。一个TIMESTAMP列对于记录一个INSERT或UPDATE操作的日期和时间是有用的，因为如果你不自己给它赋值，它自动地被设置为最近操作的日期和时间。你以可以通过赋给它一个NULL值设置它为当前的日期和时间

2016-04-23

数据库

数据库索引实现原理

索引问题就是一个查找问题。。。

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

索引的优缺点

优点

创建索引可以大大提高系统的性能。

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

建立索引的条件

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

应该建立索引的列

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不应该建立索引的列

对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

索引种类

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。
当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

主键索引

数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。
如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

B-/+Tree索引的性能分析

到这里终于可以分析B-/+Tree索引的性能了。
上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：
每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。
B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。
而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

综上所述，用B-Tree作为索引结构效率是非常高的。

B树和B+树

B树

B树中每个节点包含了键值和键值对于的数据对象存放地址指针，所以成功搜索一个对象可以不用到达树的叶节点。

成功搜索包括节点内搜索和沿某一路径的搜索，成功搜索时间取决于关键码所在的层次以及节点内关键码的数量。

在B树中查找给定关键字的方法是：首先把根结点取来，在根结点所包含的关键字K1,…,kj查找给定的关键字（可用顺序查找或二分查找法），若找到等于给定值的关键字，则查找成功；否则，一定可以确定要查的关键字在某个Ki或Ki+1之间，于是取Pi所指的下一层索引节点块继续查找，直到找到，或指针Pi为空时查找失败。

B+树

B+树非叶节点中存放的关键码并不指示数据对象的地址指针，非也节点只是索引部分。所有的叶节点在同一层上，包含了全部关键码和相应数据对象的存放地址指针，且叶节点按关键码从小到大顺序链接。如果实际数据对象按加入的顺序存储而不是按关键码次数存储的话，叶节点的索引必须是稠密索引，若实际数据存储按关键码次序存放的话，叶节点索引时稀疏索引。

B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

所以 B+树有两种搜索方法：

一种是按叶节点自己拉起的链表顺序搜索。

一种是从根节点开始搜索，和B树类似，不过如果非叶节点的关键码等于给定值，搜索并不停止，而是继续沿右指针，一直查到叶节点上的关键码。所以无论搜索是否成功，都将走完树的所有层。

B+ 树中，数据对象的插入和删除仅在叶节点上进行。

这两种处理索引的数据结构的不同之处：
a，B树中同一键值不会出现多次，并且它有可能出现在叶结点，也有可能出现在非叶结点中。而B+树的键一定会出现在叶结点中，并且有可能在非叶结点中也有可能重复出现，以维持B+树的平衡。
b，因为B树键位置不定，且在整个树结构中只出现一次，虽然可以节省存储空间，但使得在插入、删除操作复杂度明显增加。B+树相比来说是一种较好的折中。
c，B树的查询效率与键在树中的位置有关，最大时间复杂度与B+树相同(在叶结点的时候)，最小时间复杂度为1(在根结点的时候)。而B+树的时候复杂度对某建成的树是固定的。

2016-04-20

操作系统

用户态和内核态

特权级

任何操作系统来说，创建一个新的进程都是属于核心功能，因为它要做很多底层细致地工作，消耗系统的物理资源，比如分配物理内存，从父进程拷贝相关信息，拷贝设置页目录页表等等，这些显然不能随便让哪个程序就能去做，于是就自然引出特权级别的概念，显然，最关键性的权力必须由高特权级的程序来执行，这样才可以做到集中管理，减少有限资源的访问和使用冲突。

特权级显然是非常有效的管理和控制程序执行的手段，因此在硬件上对特权级做了很多支持，就Intel x86架构的CPU来说一共有0~3四个特权级，0级最高，3级最低，硬件上在执行每条指令时都会对指令所具有的特权级做相应的检查，相关的概念有 CPL、DPL和RPL，这里不再过多阐述。硬件已经提供了一套特权级使用的相关机制，软件自然就是好好利用的问题，这属于操作系统要做的事情，对于 Unix/Linux来说，只使用了0级特权级和3级特权级。也就是说在Unix/Linux系统中，一条工作在级特权级的指令具有了CPU能提供的最高权力，而一条工作在3级特权级的指令具有CPU提供的最低或者说最基本权力。

用户态和内核态

现在我们从特权级的调度来理解用户态和内核态就比较好理解了，当程序运行在3级特权级上时，就可以称之为运行在用户态，因为这是最低特权级，是普通的用户进程运行的特权级，大部分用户直接面对的程序都是运行在用户态；反之，当程序运行在0级特权级上时，就可以称之为运行在内核态。
通俗点说就是

内核态: CPU可以访问内存所有数据, 包括外围设备, 例如硬盘, 网卡. CPU也可以将自己从一个程序切换到另一个程序
用户态: 只能受限的访问内存, 且不允许访问外围设备. 占用CPU的能力被剥夺, CPU资源可以被其他程序获取

用户态和内核态的切换

系统调用：这是用户态进程主动要求切换到内核态的一种方式，用户态进程通过系统调用申请使用操作系统提供的服务程序完成工作。而系统调用的机制其核心还是使用了操作系统为用户特别开放的一个中断来实现，例如Linux的int 80h中断。
异常：当CPU在执行运行在用户态下的程序时，发生了某些事先不可知的异常，这时会触发由当前运行进程切换到处理此异常的内核相关程序中，也就转到了内核态，比如缺页异常。
外围设备的中断：当外围设备完成用户请求的操作后，会向CPU发出相应的中断信号，这时CPU会暂停执行下一条即将要执行的指令转而去执行与中断信号对应的处理程序，如果先前执行的指令是用户态下的程序，那么这个转换的过程自然也就发生了由用户态到内核态的切换。比如硬盘读写操作完成，系统会切换到硬盘读写的中断处理程序中执行后续操作等。

这3种方式是系统在运行时由用户态转到内核态的最主要方式，其中系统调用可以认为是用户进程主动发起的，异常和外围设备中断则是被动的。
从触发方式上看，可以认为存在前述3种不同的类型，但是从最终实际完成由用户态到内核态的切换操作上来说，涉及的关键步骤是完全一致的，没有任何区别，都相当于执行了一个中断响应的过程，因为系统调用实际上最终是中断机制实现的，而异常和中断的处理机制基本上也是一致的。

系统调用的执行过程

系统调用, 在CPU中的实现称之为陷阱指令(Trap Instruction)
工作流程如下:

用户态程序将一些数据值放在寄存器中, 或者使用参数创建一个堆栈(stack frame), 以此表明需要操作系统提供的服务.
用户态程序执行陷阱指令
CPU切换到内核态, 并跳到位于内存指定位置的指令, 这些指令是操作系统的一部分, 他们具有内存保护, 不可被用户态程序访问=
这些指令称之为陷阱(trap)或者系统调用处理器(system call handler). 他们会读取程序放入内存的数据参数, 并执行程序请求的服务
系统调用完成后, 操作系统会重置CPU为用户态并返回系统调用的结果

2016-04-20

操作系统

用户态和内核态

特权级

任何操作系统来说，创建一个新的进程都是属于核心功能，因为它要做很多底层细致地工作，消耗系统的物理资源，比如分配物理内存，从父进程拷贝相关信息，拷贝设置页目录页表等等，这些显然不能随便让哪个程序就能去做，于是就自然引出特权级别的概念，显然，最关键性的权力必须由高特权级的程序来执行，这样才可以做到集中管理，减少有限资源的访问和使用冲突。

特权级显然是非常有效的管理和控制程序执行的手段，因此在硬件上对特权级做了很多支持，就Intel x86架构的CPU来说一共有0~3四个特权级，0级最高，3级最低，硬件上在执行每条指令时都会对指令所具有的特权级做相应的检查，相关的概念有 CPL、DPL和RPL，这里不再过多阐述。硬件已经提供了一套特权级使用的相关机制，软件自然就是好好利用的问题，这属于操作系统要做的事情，对于 Unix/Linux来说，只使用了0级特权级和3级特权级。也就是说在Unix/Linux系统中，一条工作在级特权级的指令具有了CPU能提供的最高权力，而一条工作在3级特权级的指令具有CPU提供的最低或者说最基本权力。

用户态和内核态

现在我们从特权级的调度来理解用户态和内核态就比较好理解了，当程序运行在3级特权级上时，就可以称之为运行在用户态，因为这是最低特权级，是普通的用户进程运行的特权级，大部分用户直接面对的程序都是运行在用户态；反之，当程序运行在0级特权级上时，就可以称之为运行在内核态。
通俗点说就是

内核态: CPU可以访问内存所有数据, 包括外围设备, 例如硬盘, 网卡. CPU也可以将自己从一个程序切换到另一个程序
用户态: 只能受限的访问内存, 且不允许访问外围设备. 占用CPU的能力被剥夺, CPU资源可以被其他程序获取

用户态和内核态的切换

系统调用：这是用户态进程主动要求切换到内核态的一种方式，用户态进程通过系统调用申请使用操作系统提供的服务程序完成工作。而系统调用的机制其核心还是使用了操作系统为用户特别开放的一个中断来实现，例如Linux的int 80h中断。
异常：当CPU在执行运行在用户态下的程序时，发生了某些事先不可知的异常，这时会触发由当前运行进程切换到处理此异常的内核相关程序中，也就转到了内核态，比如缺页异常。
外围设备的中断：当外围设备完成用户请求的操作后，会向CPU发出相应的中断信号，这时CPU会暂停执行下一条即将要执行的指令转而去执行与中断信号对应的处理程序，如果先前执行的指令是用户态下的程序，那么这个转换的过程自然也就发生了由用户态到内核态的切换。比如硬盘读写操作完成，系统会切换到硬盘读写的中断处理程序中执行后续操作等。

这3种方式是系统在运行时由用户态转到内核态的最主要方式，其中系统调用可以认为是用户进程主动发起的，异常和外围设备中断则是被动的。
从触发方式上看，可以认为存在前述3种不同的类型，但是从最终实际完成由用户态到内核态的切换操作上来说，涉及的关键步骤是完全一致的，没有任何区别，都相当于执行了一个中断响应的过程，因为系统调用实际上最终是中断机制实现的，而异常和中断的处理机制基本上也是一致的。