MySQL常用数据类型和建库策略

1. MYSQL 5数据类型,长度范围

1.1数值类型

MySQL支持所有标准SQL数 值数据类型.这些类型包括严格数值数据类型(INTEGER,SMALLINT,DECIMAL和NUMERIC),以及近似数值数据类型 (FLOAT,REAL和DOUBLE PRECISION).关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词.

BIT 数据类型保存位字段值,并且支持MyISAM,MEMORY,InnoDB和BDB表.作为SQL标准的扩展,MySQL也支持整数类型 TINYINT,MEDIUMINT和BIGINT.下面的表显示了需要的每个整数类型的存储和范围.

MySQL还支持选择在该类型关键字 后面的括号内指定整数值的显示宽度(例如,INT(4)).该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度.

显示宽 度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示.

当结合可选扩展属性ZEROFILL使用时, 默认补充的空格用零代替.例如,对于声明为INT(5) ZEROFILL的列,值4检索为00004.请注意如果在整数列保存超过显示宽度的一个值,当MySQL为复杂联接生成临时表时会遇到问题,因为在这些 情况下MySQL相信数据适合原列宽度.

所有整数类型可以有一个可选(非标准)属性UNSIGNED.当你想要在列内只允许非负数和该列需 要较大的上限数值范围时可以使用无符号值.

浮点和定点类型也可以为UNSIGNED.同数类型,该属性防止负值保存到列中.然而,与整数类 型不同的是,列值的上范围保持不变.

如果为一个数值列指定ZEROFILL,MySQL自动为该列添加UNSIGNED属性.

对 于浮点列类型,在MySQL中单精度值使用4个字节,双精度值使用8个字节.

FLOAT类型用于表示近似数值数据类型.SQL标准允许在关 键字FLOAT后面的括号内选择用位指定精度(但不能为指数范围).MySQL还支持可选的只用于确定存储大小的精度规定.0到23的精度对应FLOAT 列的4字节单精度.24到53的精度对应DOUBLE列的8字节双精度.

MySQL允许使用非标准语法:FLOAT(M,D)或 REAL(M,D)或DOUBLE PRECISION(M,D).这里,”(M,D)”表示该值一共显示M位整数,其中D位位于小数点后面.例如,定义为FLOAT(7,4)的一个列可以 显示为-999.9999.MySQL保存值时进行四舍五入,因此如果在FLOAT(7,4)列内插入999.00009,近似结果是999.0001.

MySQL 将DOUBLE视为DOUBLE PRECISION(非标准扩展)的同义词.MySQL还将REAL视为DOUBLE PRECISION(非标准扩展)的同义词,除非SQL服务器模式包括REAL_AS_FLOAT选项.

为了保证最大可能的可移植性,需要 使用近似数值数据值存储的代码应使用FLOAT或DOUBLE PRECISION,不规定精度或位数.

DECIMAL和NUMERIC类 型在MySQL中视为相同的类型.它们用于保存必须为确切精度的值,例如货币数据.当声明该类型的列时,可以(并且通常要)指定精度和标度;例如:

salary DECIMAL(5,2)
在该例子中,5是精度,2是标度.精度表示保存值的主要位数,标度表示小数点后面可以保存的位数.

MySQL 5.1中以二进制格式保存DECIMAL和NUMERIC值.

标准SQL要求salary列能够用5位整数位和两位小数保存任何值.因 此,在这种情况下可以保存在salary列的值的范围是从-999.99到999.99.

在标准SQL中,语法DECIMAL(M)等价于 DECIMAL(M,0).同样,语法DECIMAL等价于DECIMAL(M,0),可以通过计算确定M的值.在MySQL 5.1中支持DECIMAL和NUMERIC数据类型的变量形式.M默认值是10.

DECIMAL或NUMERIC的最大位数是65,但具 体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束.如果此类列分配的值小数点后面的位数超过指定的标度允许的范围,值被转换为该 标度.(具体操作与操作系统有关,但一般结果均被截取到允许的位数).

BIT数据类型可用来保存位字段值.BIT(M)类型允许存储M位 值.M范围为1到64.

要指定位值,可以使用b’value’符.value是一个用0和1编写的二进制值.例如,b’111’和 b’100000000’分别表示7和128.参见9.1.5节,”位字段值”.

如果为BIT(M)列分配的值的长度小于M位,在值的左边 用0填充.例如,为BIT(6)列分配一个值b’101′,其效果与分配b’000101’相同.

当要在一个数值列内保存一个超出该列允许 范围的值时,MySQL的操作取决于此时有效的SQL模式.如果模式未设置,MySQL将值裁剪到范围的相应端点,并保存裁减好的值.但是,如果模式设置 为traditional(“严格模式”),超出范围的值将被拒绝并提示错误,并且根据SQL标准插入会失败.参见5.3.2节,”SQL服务器模式”.

如 果INT列是UNSIGNED,列范围的大小相同,但其端点会变为到0和4294967295.如果你试图保存-9999999999和 9999999999,以非严格模式保存到列中的值是0和4294967296.

如果在浮点或定点列中分配的值超过指定(或默认)精度和标 度规定的范围,MySQL以非严格模式保存表示范围相应端点的值.

当MySQL没有工作在严格模式时,对于ALTER TABLE,LOAD DATA INFILE,UPDATE和多行INSERT语句,由于裁剪发生的转换将报告为警告.当MySQL工作在严格模式时,这些语句将失败,并且部分或全部值 不会插入或更改,取决于是否表为事务表和其它因素.

Type

Bytes

Minimum Value

Maximum Value

(Signed/Unsigned)

(Signed/Unsigned)

TINYINT

1

-128

127

0

255

SMALLINT

2

-32768

32767

0

65535

MEDIUMINT

3

-8388608

8388607

0

16777215

INT

4

-2147483648

2147483647

0

4294967295

BIGINT

8

-9223372036854775808

9223372036854775807

0

18446744073709551615

这 里将数字类型按照分类方法分为三类:整数类,小数类和数字类.

我所谓的”数字类”,就是指 DECIMAL 和 NUMERIC,它们是同一种类型.它严格的说不是一种数字类型,因为他们实际上是将数字以字符串形式保存的;他的值的每一位 (包括小数点) 占一个字节的存储空间,因此这种类型耗费空间比较大.但是它的一个突出的优点是小数的位数固定,在运算中不会”失真”,所以比较适合用于”价格”,”金 额”这样对精度要求不高但准确度要求非常高的字段.

小数类,即浮点数类型,根据精度的不同,有 FLOAT 和 DOUBLE 两种.它们的优势是精确度,FLOAT 可以表示绝对值非常小,小到约 1.17E-38 (0.000…0117,小数点后面有 37 个零) 的小数,而 DOUBLE 更是可以表示绝对值小到约 2.22E-308 (0.000…0222,小数点后面有 307 个零) 的小数.FLOAT 类型和 DOUBLE 类型占用存储空间分别是 4 字节和 8 字节.如果需要用到小数的字段,精度要求不高的,当然用 FLOAT 了.可是说句实在话,我们”民用”的数据,哪有要求精度那么高的呢?这两种类型至今我没有用过――我还没有遇到适合于使用它们的事例.

用 的最多的,最值得精打细算的,是整数类型.从只占一个字节存储空间的 TINYINT 到占 8 个字节的 BIGINT,挑选一个”够用”并且占用存储空间最小的类型是设计数据库时应该考虑的.TINYINT,SMALLINT,MEDIUMINT,INT 和 BIGINT 占用存储空间分别为 1 字节,2 字节,3 字节,4 字节和 8 字节,就无符号的整数而言,这些类型能表示的最大整数分别为 255,65535,16777215,4294967295 和 18446744073709551615.如果用来保存用户的年龄 (举例来说,数据库中保存年龄是不可取的),用 TINYINT 就够了;九城的《纵横》里,各项技能值,用 SMALLINT 也够了;如果要用作一个肯定不会超过 16000000 行的表的 AUTO_INCREMENT 的 IDENTIFY 字段,当然用 MEDIUMINT 不用 INT,试想,每行节约一个字节,16000000 行可以节约 10 兆多.

1.2日期和时 间类型

表示时间值的DATE和时间类型为DATETIME,DATE,TIMESTAMP,TIME和YEAR.每个时间类型有一 个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值.TIMESTAMP类型有专有的自动更新特性,将在后面描述.

如 果试图插入一个不合法的日期,MySQL将给出警告或错误.可以使用ALLOW_INVALID_DATES SQL模式让MySQL接受某些日期,例如’1999-11-31′.当你想要保存一个”可能错误的”用户已经在数据库中指定(例如,以web形式)用于 将来处理的值时很有用.在这种模式下,MySQL只验证月范围为从0到12,日范围为从0到31.这些范围可以包括零,因为MySQL允许在DATE或 DATETIME列保存日/月和日是零的日期.这在应用程序需要保存一个你不知道确切日期的生日时非常有用.在这种情况下,只需要将日期保存为 ‘1999-00-00’或’1999-01-00′.如果保存此类日期,DATE_SUB()或DATE_ADD等需要完整日期的函数不会得到正确的结 果.(如果你不想在日期中出现零,可以使用NO_ZERO_IN_DATE SQL模式).

MySQL还允许将’0000-00-00’保 存为”伪日期”(如果不使用NO_ZERO_DATE SQL模式).这在某些情况下比使用NULL值更方便(并且数据和索引占用的空间更小).

sql_mode系统变量设置为相应模式值,可以更确切你想让MySQL支持哪种日期.参见5.3.2节,”SQL服务器模式”.

当使用日 期和时间类型时应记住以下几点:

· MySQL以标准输出格式检索给定日期或时间类型的值,但它尽力解释你指定的各种输入值格式(例如,当你指定一个分配给或与日期或时间类型进行比较的值 时).只支持下面章节中描述的格式.期望你能提供有效值.如果你使用其它格式的值会发生意想不到的结果.

· 包含两位年值的日期会令人模糊,因为世纪不知道.MySQL使用以下规则解释两位年值:

o 70-99范围的年值转换为1970-1999.

o 00-69范围的年值转换为2000-2069.

· 尽管MySQL尝试解释几种格式的值,日期总是以年-月-日顺序(例如,’98-09-04′),而不是其它地方常用的月-日-年或日-月-年顺序(例 如,’09-04-98′,’04-09-98′).

· 如果值用于数值上下文中,MySQL自动将日期或时间类型的值转换为数字,反之亦然.

· 当 MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时(如本节开始所描述),它将该值转换为该类的”零”值.一个例外是超出范围的 TIME值被裁剪到TIME范围的相应端点.

下面的表显示了各类”零”值的格式.请注意如果启用NO_ZERO_DATE SQL模式,使用这些值会产生警告.

Data Type

“Zero” Value

DATETIME

‘0000-00-00 00:00:00’

DATE

‘0000-00-00’

TIMESTAMP

‘0000-00-00 00:00:00’

TIME

’00:00:00′

YEAR

0000

1.3 String类型

字符串类型指CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM和 SET.该节描述了这些类型如何工作以及如何在查询中使用这些类型.

CHAR和VARCHAR类型类似,但它们保存和检索的方式不 同.它们的最大长度和是否尾部空格被保留等方面也不同.在存储或检索过程中不进行大小写转换.

CHAR和VARCHAR类型声明的 长度表示你想要保存的最大字符数.例如,CHAR(30)可以占用30个字符.

CHAR列的长度固定为创建表时声明的长度.长度可以为从0 到255的任何值.当保存CHAR值时,在它们的右边填充空格以达到指定的长度.当检索到CHAR值时,尾部的空格被删除掉.在存储或检索过程中不进行大 小写转换.

VARCHAR列中的值为可变长字符串.长度可以指定为0到65,535之间的值.(VARCHAR的最大有效长度由最大行大小 和使用的字符集确定.整体最大长度是65,532字节).

同CHAR对比,VARCHAR值保存时只保存需要的字符数,另加一个字节来记录 长度(如果列声明的长度超过255,则使用两个字节).

VARCHAR值保存时不进行填充.当值保存和检索时尾部的空格仍保留,符合标准 SQL.

如果分配给CHAR或VARCHAR列的值超过列的最大长度,则对值进行裁剪以使其适合.如果被裁掉的字符不是空格,则会产生一条 警告.如果裁剪非空格字符,则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入.

下面的表显示了将各种字符串值保存到 CHAR(4)和VARCHAR(4)列后的结果,说明了CHAR和VARCHAR之间的差别:

Value

CHAR(4)

Storage Required

VARCHAR(4)

Storage Required

‘ ‘

4 bytes

1 byte

‘ab’

‘ab ‘

4 bytes

‘ab’

3 bytes

‘abcd’

‘abcd’

4 bytes

‘abcd’

5 bytes

‘abcdefgh’

‘abcd’

4 bytes

‘abcd’

5 bytes

请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格 模式,超过列长度不的值不保存,并且会出现错误.

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同,因为检索时从CHAR列 删除了尾部的空格.通过下面的例子说明该差别:

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO vc VALUES (‘ab ‘, ‘ab ‘);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT(v, ‘+’), CONCAT(c, ‘+’) FROM vc;

BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串.也就是 说,它们包含字节字符串而不是字符字符串.这说明它们没有字符集,并且排序和比较基于列值字节的数值值.

BINARY和 VARBINARY允许的最大长度一样,如同CHAR和VARCHAR,不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度.

BINARY 和VARBINARY数据类型不同于CHAR BINARY和VARCHAR BINARY数据类型.对于后一种类型,BINARY属性不会将列视为二进制字符串列.相反,它致使使用列字符集的二元 校对规则,并且列自身包含非二进制字符字符串而不是二进制字节字符串.例如CHAR(5) BINARY被视为CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin,假定默认字符集是latin1.这不同于BINARY(5),它保存5字节二进制字符串,没有字符集或 校对规则.

当 保存BINARY值时,在它们右边填充值以达到指定长度.填充值是0x00(零字节).插入值时在右侧添加0x00 on,并且选择时不删除尾部的字节.比较时所有字节很重要,包括ORDER BY和DISTINCT操作.比较时0x00字节和空格是不同的,0x00<空格.

例如:对于一个BINARY(3)列,当插入时 ‘a’ 变为 ‘a ‘.’a插入时变为’a��‘.当选择时两个插入的值均不更改.

对于VARBINARY,插入时不填充字符,选 择时不裁剪字节.比较时所有字节很重要,包括ORDER BY和DISTINCT操作.比较时0x00字节和空格是不同的,0x00<空格.

对 于尾部填充字符被裁剪掉或比较时将它们忽视掉的情形,如果列的索引需要唯一的值,在列内插入一个只是填充字符数不同的值将会造成复制键值错误.

如 果你计划使用这些数据类型来保存二进制数据并且需要检索的值与保存的值完全相同,应考虑前面所述的填充和裁剪特征.下面的例子说明了用0x00填充的 BINARY值如何影响列值比较:

mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t SET c = ‘a’;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(c), c = ‘a’, c = ‘a��‘ from t;
+——–+———+————-+
| HEX(c) | c = ‘a’ | c = ‘a
��‘ |
+——–+———+————-+
| 610000 | 0 | 1 |
+——–+———+————-+
1 row in set (0.09 sec)
如果检索的值必须与指定进行存储而没有填充的值相同,最好使用BLOB数据类型.

BLOB是一个二进制 大对象,可以容纳可变数量的数据.有4种BLOB类型:TINYBLOB,BLOB,MEDIUMBLOB和LONGBLOB.它们只是可容纳值的最大长 度不同.

有4种TEXT类型:TINYTEXT,TEXT,MEDIUMTEXT和LONGTEXT.这些对应4种BLOB类型, 有相同的最大长度和存储需求.

BLOB 列被视为二进制字符串(字节字符串).TEXT列被视为非二进制字符串(字符字符串).BLOB列没有字符集,并且排序和比较基于列值字节的数值 值.TEXT列有一个字符集,并且根据字符集的 校对规则对值进行排序和比较.

在TEXT或BLOB列的存储或检索过程中,不存在大小写转 换.

当未运行在严格模式时,如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值,值被截取以保证适合.如果截掉的字符不是 空格,将会产生一条警告.使用严格SQL模式,会产生错误,并且值将被拒绝而不是截取并给出警告.

在大多数方面,可以将BLOB列视为能够 足够大的VARBINARY列.同样,可以将TEXT列视为VARCHAR列.BLOB和TEXT在以下几个方面不同于VARBINARY和 VARCHAR:

· 当保存或检索BLOB和TEXT列的值时不删除尾部空格.(这与VARBINARY和VARCHAR列相同).

请 注意比较时将用空格对TEXT进行扩充以适合比较的对象,正如CHAR和VARCHAR.

· 对于BLOB和TEXT列的索引,必须指定索引前缀的长度.对于CHAR和VARCHAR,前缀长度是可选的.

· BLOB和TEXT列不能有 默认值.

LONG和LONG VARCHAR对应MEDIUMTEXT数据类型.这是为了保证兼容性.如果TEXT列类型使用BINARY属性,将为列分配列字符集的二元 校对规则.

MySQL 连接程序/ODBC将BLOB值定义为LONGVARBINARY,将TEXT值定义为LONGVARCHAR.

由于BLOB和TEXT值 可能会非常长,使用它们时可能遇到一些约束:

· 当排序时只使用该列的前max_sort_length个字节.max_sort_length的 默认值是1024;该值可以在启动mysqld服务器时使用–max_sort_length选项进行更改.

运行时增加 max_sort_length的值可以在排序或组合时使更多的字节有意义.任何客户端可以更改其会话max_sort_length变量的值:

mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM tbl_name
-> ORDER BY comment;
当你想要使超过max_sort_length的字节有意义,对含长值 的BLOB或TEXT列使用GROUP BY或ORDER BY的另一种方式是将列值转换为固定长度的对象.标准方法是使用SUBSTRING函数.例如,下面的语句对comment列的2000个字节进行排序:

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name
-> ORDER BY SUBSTRING(comment,1,2000);
· BLOB
或TEXT对象的最大大小由其类型确定,但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定.你可以通过更改 max_allowed_packet变量的值更改消息缓存区的大小,但必须同时修改服务器和客户端程序.例如,可以使用 mysql和mysqldump来更改客户端的max_allowed_packet值.参见7.5.2节,”调节服务器参数”,8.3 节,”mysql:MySQL命令行工具”和8.8节,”mysqldump:数据库备份程序”.

每个BLOB或TEXT值分别由内部分配 的对象表示.这与其它列类型形成对比,后者是当打开表时为每1列分配存储引擎.

不要以为字符类型就是 CHAR,CHAR 和 VARCHAR 的区别在于 CHAR 是固定长度,只要你定义一个字段是 CHAR(10),那么不论你存储的数据是否达到了 10 个字节,它都要占去 10 个字节的空间;而 VARCHAR 则是可变长度的,如果一个字段可能的值是不固定长度的,我们只知道它不可能超过 10 个字符,把它定义为 VARCHAR(10) 是最合算的,VARCHAR 类型的占用空间是它的值的实际长度 +1.为什么要 +1 呢?这一个字节用于保存实际使用了多大的长度.从这个 +1 中也应该看到,如果一个字段,它的可能值最长是 10 个字符,而多数情况下也就是用到了 10 个字符时,用 VARCHAR 就不合算了:因为在多数情况下,实际占用空间是 11 个字节,比用 CHAR(10) 还多占用一个字节.

举 个例子,就是一个存储股票名称和代码的表,股票名称绝大部分是四个字的,即 8 个字节;股票代码,上海的是六位数字,深圳的是四位数字.这些都是固定长度的,股票名称当然要用 CHAR(8);股票代码虽然是不固定长度,但如果使用 VARCHAR(6),一个深圳的股票代码实际占用空间是 5 个字节,而一个上海的股票代码要占用 7 个字节!考虑到上海的股票数目比深圳的多,那么用 VARCHAR(6) 就不如 CHAR(6) 合算了.
虽然一个 CHAR 或 VARCHAR 的最大长度可以到 255,我认为大于 20 的 CHAR 是几乎用不到的――很少有大于 20 个字节长度的固定长度的东东吧?不是固定长度的就用 VARCHAR.大于 100 的 VARCHAR 也是几乎用不到的――比这更大的用 TEXT 就好了.TINYTEXT,最大长度为 255,占用空间也是实际长度 +1;TEXT,最大长度 65535,占用空间是实际长度 +2;MEDIUMTEXT,最大长度 16777215,占用空间是实际长度 +3;LONGTEXT,最大长度 4294967295,占用空间是实际长度 +4.为什么 +1,+2,+3,+4?你要是还不知道就该打 PP 了.这些可以用在论坛啊,新闻啊,什么的,用来保存文章的正文.根据实际情况的不同,选择从小到大的不同类型.

ENUM是 一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值.

在某些情况下,ENUM值也可以为空字符串(”)或NULL:

· 如果你将一个非法值插入ENUM(也就是说,允许的值列之外的字符串),将插入空字符串以作为特殊错误值.该字符串与”普通”空字符串不同,该字符串有数 值值0.后面有详细讨论.

· 如果将ENUM列声明为允许NULL,NULL值则为该列的一个有效值,并且 默认值为NULL.如果ENUM列被声明为NOT NULL,其默认值为允许的值列的第1个元素.

每个枚举值有一个索引:

· 来自列规定的允许的值列中的值从1开始编号.

· 空字符串错误值的索引值是0.这说明你可以使用下面的SELECT语句来找出分配了非法ENUM值的行:

· mysql> SELECT * FROM tbl_name WHERE enum_col=0;
· NULL
值的索引是NULL.

例如,定义 为ENUM的列(‘one’,’two’,’three’)可以有下面所示任何值.还显示了每个值的索引:

Value

Index

NULL

NULL

0

‘one’

1

‘two’

2

‘three’

3

枚举最多可以有65,535个元素.

当 创建表时,ENUM成员值的尾部空格将自动被删除.

当检索时,保存在ENUM列的值使用列定义中所使用的大小写来显示.请注意可以为 ENUM列分配字符集和 校对规则.对于二进制或大小写敏感的校对规则,当为列分配值时应考虑大小写.

如果在数值上下文中检索一个ENUM 值,将返回列值的索引.例如,你可以这样从ENUM列搜索数值值:

mysql> SELECT enum_col+0 FROM tbl_name;
如果将一个数字保存到ENUM列,数字被视为索引,并且保存的值是该索引对应的枚举成员.(但是,这不适合LOAD DATA,它将所有输入视为字符串).不建议使用类似数字的枚举值来定义一个ENUM列,因为这很容易引起混淆.例如,下面的列含有字符串值 ‘0’,’1’和’2’的枚举成员,但数值索引值为1,2和3:

numbers ENUM(‘0′,’1′,’2’)
根据枚举成员 在列定义中列出的顺序对ENUM值进行排序.(换句话说,ENUM值根据索引编号进行排序).例如,对于ENUM(‘a’,’b’),’a’排在’b’前 面,但对于ENUM(‘b’,’a’),’b’排在’a’前面.空字符串排在非空字符串前面,并且NULL值排在所有其它枚举值前面.要想防止意想不到的 结果,按字母顺序规定ENUM列.还可以使用GROUP BY CAST(col AS CHAR)或GROUP BY CONCAT(col)来确保按照词汇对列进行排序而不是用索引数字.

如果你想要确定一个ENUM列的所有可能的值,使用SHOW COLUMNS FROM tbl_name LIKE enum_col,并解析输出中第2列的ENUM定义.

2.字段列类型存储 条件说明

根据类别列出了MySQL支持的每个列类型的存储需求.

MyISAM表中行的最大大小为65,534字节. 每个BLOB和TEXT列 账户只占其中的5至9个字节.

如果MyISAM表包括变长列类型,记录格式也是可变长度.当创建表时,在某些条 件下,MySQL可以将一个列从变长类型改为固定长度的类型或反之亦然.

数 值类型存储规则

Data Type

Storage Required

TINYINT

1 byte

SMALLINT

2 bytes

MEDIUMINT

3 bytes

INT, INTEGER

4 bytes

BIGINT

8 bytes

FLOAT(p)

4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53

FLOAT

4 bytes

DOUBLE [PRECISION], REAL

8 bytes

DECIMAL(M,D), NUMERIC(M,D)

Varies; see following discussion

BIT(M)

approximately (M+7)/8 bytes

DECIMAL(和NUMERIC)的存储需求与具体 版本有关:

使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值.每个值的整数和分数部分的存储分别确定.每 个9位数的倍数需要4个字节,并且”剩余的”位需要4个字节的一部分.下表给出了超出位数的存储需求:

Leftover Digits

Number of Bytes

0

0

1

1

2

1

3

2

4

2

5

3

6

3

7

4

8

4

日期和时间类型的存储需求

Data Type

Storage Required

DATE

3 bytes

TIME

3 bytes

DATETIME

8 bytes

TIMESTAMP

4 bytes

YEAR

1 byte

The storage requirements shown in the table arise from the way that MySQL represents temporal values:

DATE: A three-byte integer packed as DD + MM×32 + YYYY×16×32

TIME: A three-byte integer packed as DD×24×3600 + HH×3600 + MM×60 + SS

DATETIME: Eight bytes:

A four-byte integer packed as YYYY×10000 + MM×100 + DD

A four-byte integer packed as HH×10000 + MM×100 + SS

TIMESTAMP: A four-byte integer representing seconds UTC since the epoch (‘1970-01-01 00:00:00’ UTC)

YEAR: A one-byte integer

字符串类型的存储需求

Data Type

Storage Required

CHAR(M)

M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set

BINARY(M)

M bytes, 0 <= M <= 255

VARCHAR(M), VARBINARY(M)

L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes

TINYBLOB, TINYTEXT

L + 1 bytes, where L < 28

BLOB, TEXT

L + 2 bytes, where L < 216

MEDIUMBLOB, MEDIUMTEXT

L + 3 bytes, where L < 224

LONGBLOB, LONGTEXT

L + 4 bytes, where L < 232

ENUM(‘value1‘,’value2‘,…)

1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)

SET(‘value1‘,’value2‘,…)

1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

VARCHAR,BLOB 和TEXT类是变长类型.每个类型的存储需求取决于列值的实际长度(用前面的表中的L表示),而不是该类型的最大可能的大小.例 如,VARCHAR(10)列可以容纳最大长度为10的字符串.实际存储需求是字符串(L)的长度,加上一个记录字符串长度的字节.对于字符串 ‘abcd’,L是4,存储需要5个字节.

对于CHAR,VARCHAR和TEXT类型,前面的表中的值L和M应解释为字符数目,并且列定 义中的这些类型的长度表示字符数目.例如,要想保存一个TINYTEXT值需要L字符+ 1个字节.

要想计算用于保存具体 CHAR,VARCHAR或者TEXT列值的字节数,需要考虑该列使用的字符集.在具体情况中,当使用Unicode时,必须记住所有Unicode字符 使用相同的字节数.为了细分用于不同类Unicode字符使用的存储,

注释:VARCHAR列的有效最大长度为65,532字符

NDBCLUSTER 引擎只支持固定宽度的列.这说明MySQL簇中的表中的VARCHAR列的行为如同类型CHAR(不同的是每个记录仍然有一个额外字节空间).例如,在 Cluster表中,声明为VARCHAR(100)的列中的每个记录存储时将占用101个字节,无论实际存储的记录中的字符串的长度为多少.

BLOB 和TEXT类需要 1,2,3或者4个字节来记录列值的长度,取决于该类的最大可能的长度.

NDB Cluster存储引擎中,TEXT和BLOB列的实施是不同的,其中TEXT列中的每个记录由两个单独部分组成.一个是固定大小(256字节),并且实 际上保存在原表中.另一个包括超出256字节的任何数据,保存在隐含的表中.第2个表中的记录总是2,000字节长.这说明如果size<= 256,TEXT列的大小为256(其中size表示记录的大小);否则,大小是256 +size+(2000–(size–256)%2000).

ENUM 对象的大小由不同的枚举值的数目确定.枚举用一个字节,可以有255个可能的值.当枚举的值位于256和65,535之间时,用两个字节.参见 11.4.4节,”ENUM类型”.

SET对象的大小由不同的set成员的数量确定.如果set大小是N,对象占(N+7)/8个字节,四 舍五入到1,2,3,4或者8个字节.SET最多可以有64个成员.

3.选择正确的列类型

为了优化存储,在任 何情况下均应使用最精确的类型.例如,如果列的值的范围为从1到99999,若使用整数,则MEDIUMINT UNSIGNED是好的类型.在所有可以表示该列值的类型中,该类型使用的存储最少.

用精度为65位十进制数(基于10)对DECIMAL 列进行所有基本计算(+,-,*,/).

使用双精度操作对DECIMAL值进行计算.如果准确度不是太重要或如果速度为最高优先 级,DOUBLE类型即足够了.为了达到高精度,可以转换到保存在BIGINT中的定点类型.这样可以用64位整数进行所有计算,根据需要将结果转换回浮 点值.

4.使用来自其他数据库引擎的列类型

为了使用由其它卖方编写的SQL执行代码,MySQL按 照下表所示对列类型进行映射.通过这些映射,可以很容易地从其它数据库引擎将表定义导入到MySQL中:

Other Vendor Type

MySQL Type

BOOL

TINYINT

BOOLEAN

TINYINT

CHARACTER VARYING(M)

VARCHAR(M)

FIXED

DECIMAL

FLOAT4

FLOAT

FLOAT8

DOUBLE

INT1

TINYINT

INT2

SMALLINT

INT3

MEDIUMINT

INT4

INT

INT8

BIGINT

LONG VARBINARY

MEDIUMBLOB

LONG VARCHAR

MEDIUMTEXT

LONG

MEDIUMTEXT

MIDDLEINT

MEDIUMINT

NUMERIC

DECIMAL

在 创建表时对列类型进行映射,然后原来的类型定义被丢弃.如果你使用其它卖方的类型创建一个表,然后执行DESCRIBE tbl_name语句,MySQL使用等效的MySQL类型来报告表的结构.例如:

mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG, d NUMERIC);
Query OK, 0 rows affected (0.08 sec)

mysql> DESCRIBE t;
+——-+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————+——+—–+———+——-+
| a | tinyint(1) | YES | | NULL | |
| b | double | YES | | NULL | |
| c | mediumtext | YES | | NULL | |
| d | decimal(10,0) | YES | | NULL | |
+——-+—————+——+—–+———+——-+
4 rows in set (0.00 sec)

更多请参考对应版本的mysql手册阅读

MySQL 5.1参考手册

http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/

英文版本见官方网站

http://dev.mysql.com/doc/


 

MySQL配置优化

mysql配置优化

网 站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL 的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL 默认的系统参数能够让 MySQL运行得非常顺畅。

通过在网络上查找资料和自己的尝试,我认为以下系统参数是比较关键的:

(1)、back_log:
要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值 对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表,发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时,就要加大 back_log 的值了。默认数值是50,我把它改为500。

(2)、interactive_timeout:
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。 默认数值是28800,我把它改为7200。

(3)、key_buffer_size:
索 引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能 负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。默认数值是8388600(8M),我的MySQL主机有2GB内存,所以我把它改为 402649088(400MB)。

(4)、max_connections:
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 Too many connections 错误。 默认数值是100,我把它改为1024 。

(5)、record_buffer:
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是 131072(128K),我把它改为16773120 (16M)

(6)、sort_buffer:
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M),我把它改为 16777208 (16M)。
(7)、table_cache:
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。默认数值是64,我把它改为512。

(8)、thread_cache_size:
可 以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以 这个变量值。通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。我把它设置为 80。

(10)、wait_timeout:
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800,我把它改为7200。

注:参数的调整可以通过修改 /etc/my.cnf 文件并重启 MySQL 实现。这是一个比较谨慎的工作,上面的结果也仅仅是我的一些看法,你可以根据你自己主机的硬件情况(特别是内存大小)进一步修改。

MySQL分钟数据类型的区别

char、varchar、text、ntext、 bigint、int、smallint、tinyint和bit的区别及数据库的数据类型

Varchar 对每个英文(ASCII)字符都占用2个字节,对一个汉字也只占用两个字节
char 对英文(ASCII)字符占用1个字节,对一个汉字占用2个字节Varchar 的类型不以空格填满,比如varchar(100),但它的值只是"qian",则它的值就是"qian"而char 不一样,比如char(100),它的值是"qian",而实际上它在数据库中是"qian "(qian后共有96个空格,就是把它填满为100个字节)。
由于char是以固定长度的,所以它的速度会比varchar快得多!但程序处 理起来要麻烦一点,要用trim之类的函数把两边的空格去掉!
ntext
可变长度 Unicode 数据的最大长度为 230 – 1 (1,073,741,823) 个字符。存储大小是所输入字符个数的两倍(以字节为单位)。ntext 在 SQL-92 中的同义词是 national text。
text
服务器代码页中的可变长度非 Unicode 数据的最大长度为 231-1 (2,147,483,647) 个字符。当服务器代码页使用双字节字符时,存储量仍是 2,147,483,647 字节。存储大小可能小于 2,147,483,647 字节(取决于字符串)。
bigint:从-2^63(-9223372036854775808)到 2^63-1(9223372036854775807)的整型数据,存储大小为 8 个字节。
int:从 -2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据,存储大小为 4 个字节。
smallint:从 -2^15(-32,768)到2^15-1(32,767)的整数数据,存储大小为 2 个字节。
tinyint:从0到255的整数数据,存储大 小为 1 字节。
bit:1或0的整数数据,存储大小为 1 字节。

Unicode 数据
在 Microsoft&reg; SQL Server&#8482; 2000 中,传统上非 Unicode 数据类型允许使用由特定字符集定义的字符。字符集是在安装 SQL Server 时选择的,不能更改。使用 Unicode 数据类型,列可存储由 Unicode 标准定义的任何字符,包含由不同字符集定义的所有字符。Unicode 数据类型需要相当于非 Unicode 数据类型两倍的存储空间。
Unicode 数据使用 SQL Server 中的 nchar、varchar 和 ntext 数据类型进行存储。对于存储来源于多种字符集的字符的列,可采用这些数据类型。当列中各项所包含的 Unicode 字符数不同时(至多为 4000),使用 nvarchar 类型。当列中各项为同一固定长度时(至多为 4000 个 Unicode 字符),使用 nchar 类型。当列中任意项超过 4000 个 Unicode字符时,使用 ntext 类型。
说明    SQL Server 的 Unicode 数据类型是基于 SQL-92 标准中的国家字符数据类型。SQL-92 使用前缀字符 n 标识这些数据类型及其值。

1.数据类型
数据类弄是数据的一种属 性,表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然,不同的程序语言都具有不同的特点,所定义的数据类型的各类和名称都或多 或少有些不同。SQL Server 提供了 25 种数据类型:
·Binary [(N)]
·Varbinary [(N)]
·Char [(N)]
·Varchar[(N)]
·Nchar[(N)]
·Nvarchar[(N)]
·Datetime
·Smalldatetime
·Decimal[(p[,s])]
·Numeric[(p[,s])]
·Float[(N)]
·Real
·Int
·Smallint
·Tinyint
·Money
·Smallmoney
·Bit
·Cursor
·Sysname
·Timestamp
·Uniqueidentifier
·Text
·Image
·Ntext

(1)二进制数据类型
二进制数据包括 Binary、Varbinary 和 Image.
Binary 数据类型既可以是固定长度的(Binary),也可以是变长度的。
Binary[(N)] 是 n 位固定的二进制数据。其中,n 的取值范围是从 1 到 8000。其存储窨的大小是 n + 4 个字节。
       Varbinary[(N)] 是 n 位变长度的二进制数据。其中,n 的取值范围是从 1 到 8000。其存储窨的大小是 n + 4个字节,不是 n 个字节。
      在 Image 数据类型中存储的数据是以位字符串存储的,不是由 SQL Server 解释的,必须由应用程序来解释。例如,应用程序可以使用 BMP、TIEF、GIF 和 JPEG 格式把数据存储在 Image 数据类型中。

(2) 字符数据类型
         字符数据的类型包括 Char,Varchar 和 Text。
         字符数据是由任何字母、符号和数字任意组合而成的数据。
       Varchar 是变长字符数据,其长度不超过 8KB。Char 是定长字符数据,其长度最多为 8KB。超过 8KB 的ASCII 数据可以使用Text 数据类型存储。例如,因为 Html 文档全部都是 ASCII 字符,并且在一般情况下长度超过 8KB,所以这些文档可以 Text 数据类型存储在 SQL Server 中。

(3)Unicode 数据类型
        Unicode 数据类型包括 Nchar,Nvarchar 和Ntext。
在 Microsoft SQL Server 中,传统的非 Unicode 数据类型允许使用由特定字符集定义的字符。在 SQL Server 安装过程中,允许选择一种字符集。使用 Unicode 数据类型,列中可以存储任何由Unicode 标准定义的字符。在 Unicode 标准中,包括了以各种字符集定义的全部字符。使用Unicode 数据类型,所战胜的窨是使用非 Unicode 数据类型所占用的窨大小的两倍。
在 SQL Server 中,Unicode 数据以 Nchar、Nvarchar 和 Ntext 数据类型存储。使用这种字符类型存储的列可以存储多个字符集中的字符。当列的长度变化时,应该使用 Nvarchar 字符类型,这时最多可以存储 4000 个字符。当列的长度固定不变时,应该使用 Nchar 字符类型,同样,这时最多可以存储 4000 个字符。当使用 Ntext 数据类型时,该列可以存储多于 4000 个字符。

(4)日期和时间数据类型
        日期和时间数据类型包括 Datetime 和 Smalldatetime 两种类型。
日期和时间数据类型由有效的日期和时间组成。例如,有效的日期和时间数据包 括"4/01/98 12:15:00:00:00 PM"和"1:28:29:15:01 AM 8/17/98"。前一个数据类型是日期在前,时间在后一个数据类型是霎时间在前,日期在后。在 Microsoft SQL Server 中,日期和时间数据类型包括Datetime 和 Smalldatetime 两种类型时,所存储的日期范围是从 1753 年 1 月 1 日开始,到 9999 年12 月 31 日结束(每一个值要求 8 个存储字节)。使用 Smalldatetime 数据类型时,所存储的日期范围是 1900 年 1 月 1日 开始,到 2079 年 12 月 31 日结束(每一个值要求 4 个存储字节)。
日期的格式可以设定。设置 日期格式的命令如下:
Set DateFormat {format | @format _var|
其中,format | @format_var 是日期的顺序。有效的参数包括 MDY、DMY、YMD、YDM、MYD 和 DYM。在默认情况下,日期格式为 MDY。
例 如,当执行 Set DateFormat YMD 之后,日期的格式为年 月 日 形式;当执行 Set DateFormat DMY 之后,日期的格式为 日 月有年 形式

(5)数字数据类型
       数字数据只包含数字。数字数据类型包括正数和负数、小数(浮点数)和整数 。
整数由正整数和负整数组成,例如 39、25、0-2 和 33967。在 Micrsoft SQL Server 中,整数存储的数据类型是 Int,Smallint 和 Tinyint。Int 数据类型存储数据的范围大于 Smallint 数据类型存储数据的范围,而 Smallint 据类型存储数据的范围大于 Tinyint 数据类型存储数据的范围。使用 Int 数据狗昔存储数据的范围是从 -2 147 483 648 到 2 147 483 647(每一个值要求 4个字节存储空间)。使用 Smallint 数据类型时,存储数据的范围从 -32 768 到 32 767(每一个值要求2个字节存储空间)。使用 Tinyint 数据类型时,存储数据的范围是从0 到255(每一个值要求1个字节存储空间)。
精确小娄数据在 SQL Server 中的数据类型是 Decimal 和 Numeric。这种数据所占的存储空间根据该数据的位数后的位数来确定。
在SQL Server 中,近似小数数据的数据类型是 Float 和 Real。例如,三分之一这个分数记作。3333333,当使用近似数据类型时能准确表示。因此,从系统中检索到的数据可能与存储在该列中数据不完全一 样。
(6)货币数据表示正的或者负的货币数量 。
        在 Microsoft SQL Server 中,货币数据的数据类型是Money 和 Smallmoney。Money 数据类型要求 8 个存储字节,Smallmoney 数据类型要求 4 个存储字节。

(7)特殊数据类型
        特殊数据类型包括前面没有提过的数据类型。特殊的数据类型有3种,即 Timestamp、Bit 和 Uniqueidentifier。
       Timestamp 用于表示SQL Server 活动的先后顺序,以二进投影的格式表示。Timestamp 数据与插入数据或者日期和时间没有关系。
Bit 由 1 或者 0 组成。当表示真或者假、ON 或者 OFF 时,使用 Bit 数据类型。例如,询问是否是每一次访问的客户机请求可以存储在这种数据类型的列中。
Uniqueidentifier 由 16 字节的十六进制数字组成,表示一个全局唯一的。当表的记录行要求唯一时,GUID是非常有用。例如,在客户标识号列使用这种数据类型可以区别不同的客户。

Memcache的安装和使用

很多phper不知道如何在Windows下搭建Memcache的开发调试环境,最近个人也在研究Memcache,记录下自己安装搭建 的过程。
其实我开始研究Memcache的时候并不知道居然还有memcached for Win32这个鸟东西,害得我在CnetOS下折腾1天才搞定,今天突然发现Windows下的Memcache进行开发调试完全没有问题,所以写篇 Memcache的文档分享给大家。

Windows下的Memcache安装
1. 下载
memcache的windows稳定版,解压放某个盘下 面,比如在c:\memcached
2. 在终端(也即cmd命令界面)下输入 ‘c:\memcached\memcached.exe -d install’ 安装
3. 再输入: ‘c:\memcached\memcached.exe -d start’ 启动。NOTE: 以后memcached将作为windows的一个服务每次开机时自动启动。这样服务器端已经安装完毕了。
4.下载
php_memcache.dll,请自己查找对应的php 版本的文件
5. 在C:\winnt\php.ini 加入一行 ‘extension=php_memcache.dll’
6.重新启 动Apache,然后查看一下phpinfo,如果有memcache,那么就说明安装成功!

memcached的基本设置
-p 监听的端口
-l 连接的IP地址, 默认是本机
-d start 启动memcached服务
-d restart 重起memcached服务
-d stop|shutdown 关闭正在运行的memcached服务
-d install 安装memcached服务
-d uninstall 卸载memcached服务
-u 以的身份运行 (仅在以root运行的时候有效)
-m 最大内存使用,单位MB。默认64MB
-M 内存耗尽时返回错误,而不是删除项
-c 最大同时连接数,默认是1024
-f 块大小增长因子,默认是1.25
-n 最小分配空间,key+value+flags默认是48
-h 显示帮助

Memcache环境测试
运行下面的php文件,如果有输出This is a test!,就表示环境搭建成功。开始领略Memcache的魅力把!
< ?php
$mem = new Memcache;
$mem->connect(”127.0.0.1″, 11211);
$mem->set(’key’, ‘This is a test!’, 0, 60);
$val = $mem->get(’key’);
echo $val;
?>

参考资料
对Memcached有疑问的朋友可以参考下列文章:
Linux下的Memcache安装:http://www.ccvita.com/257.html
Windows下的Memcache安装:http://www.ccvita.com/258.html
Memcache基础教程:http://www.ccvita.com/259.html
Discuz!的Memcache缓存实现:http://www.ccvita.com/261.html
Memcache协议中文版:http://www.ccvita.com/306.html
Memcache分布式部署方案:http://www.ccvita.com/395.html


MySQL select in 排序问题

表结构如下:
mysql> select * from test;
+—-+——-+
| id | name |
+—-+——-+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
+—-+——-+

执行以下SQL:
mysql> select * from test where id in(3,1,5);
+—-+——-+
| id | name |
+—-+——-+
| 1 | test1 |
| 3 | test3 |
| 5 | test5 |
+—-+——-+
3 rows in set (0.00 sec)

这个select在mysql中得结果会自动按照id升序排列,
但是我想执行"select * from test where id in(3,1,5);"的结果按照in中得条件排序,即:3,1,5,

想得到的结果如下:
id name
3 test3
1 test1
5 test5

请问在这样的SQL在Mysql中怎么写?
网上查到sqlserver中可以用order by charindex解决,但是没看到Mysql怎么解决??请高手帮忙,谢

谢!

select * from a order by substring_index(‘3,1,2’,id,1);

试下这个good,ls正解。

order by find_in_set(id,’3,1,5′)

谢谢,经测试order by substring_index和order by find_in_set都可以

Linux下MySQL configure参数

–prefix=/program/mysql 安装路径

–localstatedir=/var/lib/mysql 数据库文件存放路径

–with-comment=Source

–with-server-suffix=-Community

–with-mysqld-user=mysql

–without-debug

–with-big-tables

–with-charset=gbk utf8

–with-collation=gbk_chinese_ci utf8_unicode_ci

–with-extra-charsets=all

–with-pthread

–enable-static

–enable-thread-safe-client

–with-client-ldflags=-all-static

–with-mysqld-ldflags=-all-static

–enable-assembler

–without-innodb

–without-isam

–without-ndb-debug

–with-unix-socket-path=/home/mysql/tmp/mysql.sock

Table ‘xxxx’ is marked as crashed…

今天VODCMS的管理员反映无法登陆后台,我想可能是Apache或者数据库又出错了。

重启了下Apache,正如所料想的一样,问题依旧。

查看了一下数据库的表,发现有个表出错了,修复不能!

Table Op Msg_type Msg_text
vodcms.vodcms_members optimize Error Table ‘.\vodcms\vodcms_members’ is marked as crash…
vodcms.vodcms_members optimize Error Table ‘vodcms_members’ is marked as crashed and la…
vodcms.vodcms_members optimize error Corrupt

网上搜了搜,发现这个错误很严重,数据基本上没有了,不管用MySQL提供的修复工具还是PhpMyAdmin都修复不能,查看了下data文件夹,发现少了个*.MYD文件。

所以呢,从新从网上下了个VODCMS5.0将错误的表重新创建,数据重新导入,

要是错误出在其他的重要的数据表上的话问题可就严重了。

记得前些时候BBS的post表也出过错,不过能修复好,要是出现这样的错误可就严重了。

所以以后记得要随时备份数据库。

MySQL外键(foreign key)的用法

在MySQL中MyISAM和InnoDB存储引擎都支持外键(foreign key),但是MyISAM只能支持语法,却不能实际使用。下面通过例子记录下InnoDB中外键的使用方法:

创建主表:
mysql> create table parent(id int not null,primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.04 sec)
创建从表:
mysql> create table child(id int,parent_id int,foreign key (parent_id) references parent(id) on delete cascade) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

插入主表测试数据:
mysql> insert into parent values(1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
插入从表测试数据:
mysql> insert into child values(1,1),(1,2),(1,3),(1,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test/child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE)

因为4不在主表中,插入时发生了外键约束错误。

只插入前三条:
mysql> insert into child values(1,1),(1,2),(1,3);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
成功!

删除主表记录,从表也将同时删除相应记录:
mysql> delete from parent where id=1;
Query OK, 1 row affected (0.03 sec)
mysql> select * from child;
+——+———–+
| id | parent_id |
+——+———–+
| 1 | 2 |
| 1 | 3 |
+——+———–+
2 rows in set (0.00 sec)

更新child中的外键,如果对应的主键不存在,则报错:
mysql> update child set parent_id=4 where parent_id=2;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test/child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE)

如果改为主表中存在的值,则可以正常更新:
mysql> update child set parent_id=2 where parent_id=2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0

如果要在父表中更新或者删除一行,并且在子表中也有一行或者多行匹配,此时子表的操作有5个选择:
· CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在两个表之间,你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。
· SET NULL: 从父表删除或更新行,并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词,这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。
· NO ACTION: 在ANSI SQL-92标准中,NO ACTION意味这不采取动作,就是如果有一个相关的外键值在被参考的表里,删除或更新主要键值的企图不被允许进行(Gruber, 掌握SQL, 2000:181)。 InnoDB拒绝对父表的删除或更新操作。
· RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样,删除ON DELETE或ON UPDATE子句。(一些数据库系统有延期检查,并且NO ACTION是一个延期检查。在MySQL中,外键约束是被立即检查的,所以NO ACTION和RESTRICT是同样的)。
· SET DEFAULT: 这个动作被解析程序识别,但InnoDB拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定义。

MySQL视图简介

一. 视图概述

视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。

对其中所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。

视图是存储在数据库中的查询的SQL 语句,它主要出于两种原因:安全原因, 视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。

  视图:查看图形或文档的方式。

  视图是从一个或多个表或视图中导出的表,其结构和数据是建立在对表的查询基础上的。和表一样,视图也是包括几个被定义的数据列和多个数据行,但就本质而言这些数据列和数据行来源于其所引用的表。

  所以视图不是真实存在的基础表而是一张虚表,视图所对应的数据并不实际地以视图结构存储在数据库中,而是存储在视图所引用的表中。

  视图一经定义便存储在数据库中,与其相对应的数据并没有像表那样又在数据库中再存储一份,通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样,可以对其进行查询、修改(有一定的限制)、删除。

  当对通过视图看到的数据进行修改时,相应的基本表的数据也要发生变化,同时,若基本表的数据发生变化,则这种变化也可以自动地反映到视图中。

  视图有很多优点,主要表现在:

  •视点集中
  •简化操作
  •定制数据
  •合并分割数据
  •安全性

二. 创建视图——CREATE VIEW

1. 语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]通过该语句可以创建视图,若给定了[OR REPLACE],则表示当已具有同名的视图时,将覆盖原视图。select_statement是一个查询语句,这个查询语句可从表或其它的视图中查询。视图属于数据库,因此需要指定数据库的名称,若未指定时,表示在当前的数据库创建新视图。

表和数据库共享数据库中相同的名称空间,因此,数据库不能包含相同名称的表和视图,并且,视图的列名也不能重复。

2. 使用举例

Eg. 本例创建一个产品表(product)和一个购买记录表(purchase),再通过视图purchase_detail查询出购买的详细信息。

CREATE TABLE product

(

product_id INT NOT NULL,

name VARCHAR(50) NOT NULL,

price DOUBLE NOT NULL

);

INSERT INTO product VALUES(1, ‘apple ‘, 5.5);

CREATE TABLE purchase

(

id INT NOT NULL,

product_id INT NOT NULL,

qty INT NOT NULL DEFAULT 0,

gen_time DATETIME NOT NULL

);

INSERT INTO purchase VALUES(1, 1, 10, NOW());

CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

创建成功后,输入:SELECT * FROM purchase_detail;

运行效果如下:

+——-+——-+—–+————-+

| name | price | qty | total_value |

+——-+——-+—–+————-+

| apple | 5.5 | 10 | 55 |

+——-+——-+—–+————-+

1 row in set (0.01 sec)

3. 注意事项

创建视图存在如下注意事项:

(1) 运行创建视图的语句需要用户具有创建视图(CRATE VIEW)的权限,若加了[OR REPLACE]时,还需要用户具有删除视图(DROP VIEW)的权限;

(2) SELECT语句不能包含FROM子句中的子查询;

(3) SELECT语句不能引用系统或用户变量;

(4) SELECT语句不能引用预处理语句参数;

(5) 在存储子程序内,定义不能引用子程序参数或局部变量;

(6) 在定义中引用的表或视图必须存在。但是,创建了视图后,能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题,可使用CHECK TABLE语句;

(7) 在定义中不能引用TEMPORARY表,不能创建TEMPORARY视图;

(8) 在视图定义中命名的表必须已存在;

(9) 不能将触发程序与视图关联在一起;

(10) 在视图定义中允许使用ORDER BY,但是,如果从特定视图进行了选择,而该视图使用了具有自己ORDER BY的语句,它将被忽略。

三. 修改视图——ALTER VIEW

1. 语法

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。

2. 使用举例

Eg. 将上一小节中中创建的视purchase_detail进行修改,去掉qty列,语句如下:

ALTER VIEW purchase_detail AS SELECT product.name as name, product .price as price, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

此时通过语句:select * from purchase_detail;对视图进行查询时,结果如下:

+——-+——-+————-+

| name | price | total_value |

+——-+——-+————-+

| apple | 5.5 | 55 |

+——-+——-+————-+

3. 注意事项

修改视图的注意事项除了第一条外跟创建视图的注意事项是一样的。第(1)条应改为:

该语句需要具有针对视图的CREATE VIEW和DROP权限,也需要针对SELECT语句中引用的每一列的某些权限。

四. 删除视图——DROP VIEW

1. 语法

DROP VIEW [IF EXISTS] view_name [, view_name] … [RESTRICT | CASCADE]该语句用户删除视图,可一次删除多个视图。[IF EXISTS]选项确保语句正确运行。若没有该子句,当指定的视图不存在时,将发生错误。

2. 使用举例

Eg1. 删除在前面的小节中创建的视图purchase_detail:DROP VIEW purchase_detail;

Eg2. 删除一个未知的视图:DROP VIEW IF EXISTS test_view;

Eg3. 删除多个视图:DROP VIEW IF EXISTS test_view1, test_view2;

3. 注意事项

必须对要删除的一个或多个视图拥有DROP VIEW的权限。