mysql-logo.gif (3082 bytes)MySQL中文参考手册

译者:晏子 ([email protected])      主页:http://linuxdb.yeah.net


第一章, 前一章, 下一章, 最后一章目录.


7 MySQL语言参考

7.1 文字:怎么写字符串和数字

7.1.1 字符串

一个字符串是一个字符序列,由单引号(“'”)或双引号(“"”)字符(后者只有你不在ANSI模式运行)包围。例如:

'a string'
"another string"

在字符串内,某个顺序有特殊的意义。这些顺序的每一个以一条反斜线(“\”)开始,称为转义字符MySQL识别下列转义字符:

\0
一个ASCII 0 (NUL)字符。
\n
一个新行符。
\t
一个定位符。
\r
一个回车符。
\b
一个退格符。
\'
一个单引号(“'”)符。
\"
一个双引号(“"”)符。
\\
一个反斜线(“\”)符。
\%
一个“%”符。它用于在正文中搜索“%”的文字实例,否则这里“%”将解释为一个通配符。
\_
一个“_”符。它用于在正文中搜索“_”的文字实例,否则这里“_”将解释为一个通配符。

注意,如果你在某些正文环境中使用“\%”“\%_”,这些将返回字符串“\%”“\_”而不是“%”“_”

有几种方法在一个字符串内包括引号:

下面显示的SELECT演示引号和转义如何工作:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "This\nIs\nFour\nlines";
+--------------------+
| This
Is
Four
lines |
+--------------------+
 

如果你想要把二进制数据插入到一个BLOB列,下列字符必须由转义序列表示:

NUL
ASCII 0。你应该用'\0'(一个反斜线和一个ASCII '0')表示它。
\
ASCII 92,反斜线。用'\\'表示。
'
ASCII 39,单引号。用“\'”表示。
"
ASCII 34,双引号。用“\"”表示。

如果你写C代码,你可以使用C API函数mysql_escape_string()来为INSERT语句转义字符。见20.3 C API 函数概述。在 Perl中,你可以使用DBI包中的quote方法变换特殊的字符到正确的转义序列。见20.5.2 DBI接口

你应该在任何可能包含上述任何特殊字符的字符串上使用转义函数!

7.1.2 数字

整数表示为一个数字顺序。浮点数使用“.”作为一个十进制分隔符。这两种类型的数字可以前置“-”表明一个负值。

有效整数的例子:

1221
0
-32

有效浮点数的例子:

294.42
-32032.6809e+10
148.00

一个整数可以在浮点上下文使用;它解释为等值的浮点数。

7.1.3 十六进制值

MySQL支持十六进制值。在数字上下文,它们表现类似于一个整数(64位精度)。在字符串上下文,它们表现类似于一个二进制字符串,这里每一对十六进制数字被变换为一个字符。

mysql> SELECT 0xa+0
       -> 10
mysql> select 0x5061756c;
       -> Paul

十六进制字符串经常被ODBC使用,给出BLOB列的值。

7.1.4 NULL

NULL值意味着“无数据”并且不同于例如数字类型的0为或字符串类型的空字符串。见18.15 NULL值问题

当使用文本文件导入或导出格式(LOAD DATA INFILE, SELECT ... INTO OUTFILE)时,NULL可以用\N表示。见7.16 LOAD DATA INFILE句法

7.1.5 数据库、表、索引、列和别名的命名

数据库、表、索引、列和别名的名字都遵守MySQL同样的规则:

注意,从MySQL3.23.6开始规则改变了,此时我们引入了用'引用的标识符(数据库、表和列命名)(如果你以ANSI模式运行,"也将用于引用标识符)。

标识符 最大长度 允许的字符
数据库 64 在一个目录名允许的任何字符,除了/.
64 在文件名中允许的任何字符,除了/.
64 所有字符
别名 255 所有字符

注意,除了以上,你在一个标识符中不能有ASCII(0)或ASCII(255)。

注意,如果标识符是一个限制词或包含特殊字符,当你使用它时,你必须总是用`引用它:

SELECT * from `select` where `select`.id > 100; 

在 MySQL的先前版本,命名规则如下:

建议你不使用象1e这样的名字,因为一个表达式如1e+1是二义性的。它可以解释为表达式1e + 1或数字1e+1

MySQL中,你能使用下列表格的任何一种引用列:

列引用 含义
col_name 来自于任意表的列col_name,用于包含该表的一个列的查询中
tbl_name.col_name 来自当前的数据库的表tbl_name的列col_name
db_name.tbl_name.col_name 行列col_name从表格tbl_name数据库db_name。这个形式在MySQL3.22或以后版本可用。
`column_name` 是一个关键词或包含特殊字符的列。

在一条语句的列引用中,你不必指定一个tbl_namedb_name.tbl_name前缀,除非引用会有二义性。例如,假定表t1t2,每个均包含列c,并且你用一个使用t1t2SELECT语句检索c。在这种情况下,c有二义性,因为它在使用表的语句中不是唯一的,因此你必须通过写出t1.ct2.c来指明你想要哪个表。同样,如果你从数据库db1中一个表t和在数据库db2的一个表t检索,你必须用db1.t.col_namedb2.t.col_name引用这些数据表的列。

句法.tbl_name意味着在当前的数据库中的表tbl_name,该句法为了ODBC的兼容性被接受,因为一些ODBC程序用一个“.”字符作为数据库表名的前缀。

7.1.5.1 名字的大小写敏感性

MySQL中,数据库和表对应于在那些目录下的目录和文件,因而,内在的操作系统的敏感性决定数据库和表命名的大小写敏感性。这意味着数据库和表名在Unix上是区分大小写的,而在Win32上忽略大小写。

注意:在Win32上,尽管数据库和表名是忽略大小写的,你不应该在同一个查询中使用不同的大小写来引用一个给定的数据库和表。下列查询将不工作,因为它作为my_table和作为MY_TABLE引用一个表:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

列名在所有情况下都是忽略大小写的。

表的别名是区分大小写的。下列查询将不工作: 因为它用aA引用别名:

mysql> SELECT col_name FROM tbl_name AS a
           WHERE a.col_name = 1 OR A.col_name = 2;

列的别名是忽略大小写的。

7.2 用户变量

MySQL支持线程特定的变量,用@variablename句法。一个变量名可以由当前字符集的数字字母字符和“_”“$”“.”组成。缺省字符集是ISO-8859-1 Latin1;这可以通过重新编译MySQL改变。见9.1.1 用于数据和排序的字符集

变量不必被初始化。缺省地,他们包含NULL并能存储整数、实数或一个字符串值。当线程退出时,对于一个线程的所有变量自动地被释放。

你可以用SET句法设置一个变量:

SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].

你也可以用@variable:=expr句法在一个表达式中设置一个变量:

select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

(这里,我们不得不使用:=句法,因为=是为比较保留的)

7.3 列类型

MySQL支持大量的列类型,它可以被分为3类:数字类型、日期和时间类型以及字符串(字符)类型。本节首先给出可用类型的一个概述,并且总结每个列类型的存储需求,然后提供每个类中的类型性质的更详细的描述。概述有意简化,更详细的说明应该考虑到有关特定列类型的附加信息,例如你能为其指定值的允许格式。

MySQL支持的列类型列在下面。下列代码字母用于描述中:

M
指出最大的显示尺寸。最大的合法的显示尺寸是 255 。
D
适用于浮点类型并且指出跟随在十进制小数点后的数码的数量。最大可能的值是30,但是应该不大于M-2。

方括号(“[”“]”)指出可选的类型修饰符的部分。

注意,如果你指定一个了为ZEROFILLMySQL将为该列自动地增加UNSIGNED属性。

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
一个很小的整数。有符号的范围是-128127,无符号的范围是0255
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
一个小整数。有符号的范围是-3276832767,无符号的范围是065535
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
一个中等大小整数。有符号的范围是-83886088388607,无符号的范围是016777215
INT[(M)] [UNSIGNED] [ZEROFILL]
一个正常大小整数。有符号的范围是-21474836482147483647,无符号的范围是04294967295
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
这是INT的一个同义词。
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
一个大整数。有符号的范围是-92233720368547758089223372036854775807,无符号的范围是018446744073709551615。注意,所有算术运算用有符号的BIGINTDOUBLE值完成,因此你不应该使用大于9223372036854775807(63位)的有符号大整数,除了位函数!注意,当两个参数是INTEGER值时,-+*将使用BIGINT运算!这意味着如果你乘2个大整数(或来自于返回整数的函数),如果结果大于9223372036854775807,你可以得到意外的结果。一个浮点数字,不能是无符号的,对一个单精度浮点数,其精度可以<=24,对一个双精度浮点数,是在25 和53之间,这些类型如FLOATDOUBLE类型马上在下面描述。FLOAT(X)有对应的FLOATDOUBLE相同的范围,但是显示尺寸和小数位数是未定义的。在MySQL3.23中,这是一个真正的浮点值。在更早的MySQL版本中,FLOAT(precision)总是有2位小数。该句法为了ODBC兼容性而提供。
FLOAT[(M,D)] [ZEROFILL]
一个小(单精密)浮点数字。不能无符号。允许的值是-3.402823466E+38-1.175494351E-3801.175494351E-383.402823466E+38。M是显示宽度而D是小数的位数。没有参数的FLOAT或有<24 的一个参数表示一个单精密浮点数字。
DOUBLE[(M,D)] [ZEROFILL]
一个正常大小(双精密)浮点数字。不能无符号。允许的值是-1.7976931348623157E+308-2.2250738585072014E-30802.2250738585072014E-3081.7976931348623157E+308。M是显示宽度而D是小数位数。没有一个参数的DOUBLEFLOAT(X)(25 < = X < = 53)代表一个双精密浮点数字。
DOUBLE PRECISION[(M,D)] [ZEROFILL]
 
REAL[(M,D)] [ZEROFILL]
这些是DOUBLE同义词。
DECIMAL[(M[,D])] [ZEROFILL]
一个未压缩(unpack)的浮点数字。不能无符号。行为如同一个CHAR列:“未压缩”意味着数字作为一个字符串被存储,值的每一位使用一个字符。小数点,并且对于负数,“-”符号不在M中计算。如果D是0,值将没有小数点或小数部分。DECIMAL值的最大范围与DOUBLE相同,但是对一个给定的DECIMAL列,实际的范围可以通过MD的选择被限制。如果D被省略,它被设置为0。如果M被省掉,它被设置为10。注意,在MySQL3.22里,M参数包括符号和小数点。
NUMERIC(M,D) [ZEROFILL]
这是DECIMAL的一个同义词。
DATE
一个日期。支持的范围是'1000-01-01''9999-12-31'MySQL'YYYY-MM-DD'格式来显示DATE值,但是允许你使用字符串或数字把值赋给DATE列。
DATETIME
一个日期和时间组合。支持的范围是'1000-01-01 00:00:00''9999-12-31 23:59:59'MySQL'YYYY-MM-DD HH:MM:SS'格式来显示DATETIME值,但是允许你使用字符串或数字把值赋给DATETIME的列。
TIMESTAMP[(M)]
一个时间戳记。范围是'1970-01-01 00:00:00'2037年的某时。MySQLYYYYMMDDHHMMSSYYMMDDHHMMSSYYYYMMDDYYMMDD格式来显示TIMESTAMP值,取决于是否M14(或省略)、1286,但是允许你使用字符串或数字把值赋给TIMESTAMP列。一个TIMESTAMP列对于记录一个INSERTUPDATE操作的日期和时间是有用的,因为如果你不自己给它赋值,它自动地被设置为最近操作的日期和时间。你以可以通过赋给它一个NULL值设置它为当前的日期和时间。见7.3.6 日期和时间类型
TIME
一个时间。范围是'-838:59:59''838:59:59'MySQL'HH:MM:SS'格式来显示TIME值,但是允许你使用字符串或数字把值赋给TIME列。
YEAR[(2|4)]
一个2或4位数字格式的年(缺省是4位)。允许的值是19012155,和0000(4位年格式),如果你使用2位,1970-2069( 70-69)。MySQLYYYY格式来显示YEAR值,但是允许你把使用字符串或数字值赋给YEAR列。(YEAR类型在MySQL3.22中是新类型。)
CHAR(M) [BINARY]
一个定长字符串,当存储时,总是是用空格填满右边到指定的长度。M的范围是1 ~ 255个字符。当值被检索时,空格尾部被删除。CHAR值根据缺省字符集以大小写不区分的方式排序和比较,除非给出BINARY关键词。NATIONAL CHAR(短形式NCHAR)是ANSI SQL的方式来定义CHAR列应该使用缺省字符集。这是MySQL的缺省。CHARCHARACTER的一个缩写。
[NATIONAL] VARCHAR(M) [BINARY]
一个变长字符串。注意:当值被存储时,尾部的空格被删除(这不同于ANSI SQL规范)。M的范围是1 ~ 255个字符。 VARCHAR值根据缺省字符集以大小写不区分的方式排序和比较,除非给出BINARY关键词值。见7.7.1 隐式列指定变化VARCHARCHARACTER VARYING一个缩写。
TINYBLOB
 
TINYTEXT
一个BLOBTEXT列,最大长度为255(2^8-1)个字符。见7.7.1 隐式列指定变化
BLOB
 
TEXT
一个BLOBTEXT列,最大长度为65535(2^16-1)个字符。见7.7.1 隐式列指定变化
MEDIUMBLOB
 
MEDIUMTEXT
一个BLOBTEXT列,最大长度为16777215(2^24-1)个字符。见7.7.1 隐式列指定变化
LONGBLOB
 
LONGTEXT
一个BLOBTEXT列,最大长度为4294967295(2^32-1)个字符。见7.7.1 隐式列指定变化
ENUM('value1','value2',...)
枚举。一个仅有一个值的字符串对象,这个值式选自与值列表'value1''value2', ...,或NULL。一个ENUM最多能有65535不同的值。
SET('value1','value2',...)
一个集合。能有零个或多个值的一个字符串对象,其中每一个必须从值列表'value1', 'value2', ...选出。一个SET最多能有64个成员。

7.3.1 列类型存储需求

对于每个由MySQL支持的列类型的存储需求在下面按类列出。

7.3.2 数字类型

列类型 需要的存储量
TINYINT 1 字节
SMALLINT 2 个字节
MEDIUMINT 3 个字节
INT 4 个字节
INTEGER 4 个字节
BIGINT 8 个字节
FLOAT(X) 4 如果 X < = 24 或 8 如果 25 < = X < = 53
FLOAT 4 个字节
DOUBLE 8 个字节
DOUBLE PRECISION 8 个字节
REAL 8 个字节
DECIMAL(M,D) M字节(D+2 , 如果M < D)
NUMERIC(M,D) M字节(D+2 , 如果M < D)

7.3.3 日期和时间类型

列类型 需要的存储量
DATE 3 个字节
DATETIME 8 个字节
TIMESTAMP 4 个字节
TIME 3 个字节
YEAR 1 字节

7.3.4 串类型

列类型 需要的存储量
CHAR(M) M字节,1 <= M <= 255
VARCHAR(M) L+1 字节, 在此L <= M1 <= M <= 255
TINYBLOB, TINYTEXT L+1 字节, 在此L< 2 ^ 8
BLOB, TEXT L+2 字节, 在此L< 2 ^ 16
MEDIUMBLOB, MEDIUMTEXT L+3 字节, 在此L< 2 ^ 24
LONGBLOB, LONGTEXT L+4 字节, 在此L< 2 ^ 32
ENUM('value1','value2',...) 1 或 2 个字节, 取决于枚举值的数目(最大值65535)
SET('value1','value2',...) 1,2,3,4或8个字节, 取决于集合成员的数量(最多64个成员)

VARCHARBLOBTEXT类型是变长类型,对于其存储需求取决于列值的实际长度(在前面的表格中用L表示),而不是取决于类型的最大可能尺寸。例如,一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串,实际的存储需要是字符串的长度(L),加上1个字节以记录字符串的长度。对于字符串'abcd'L是4而存储要求是5个字节。

BLOBTEXT类型需要1,2,3或4个字节来记录列值的长度,这取决于类型的最大可能长度。

如果一个表包括任何变长的列类型,记录格式将也是变长的。注意,当一个表被创建时,MySQL可能在某些条件下将一个列从一个变长类型改变为一个定长类型或相反。见7.7.1 隐式列指定变化

一个ENUM对象的大小由不同枚举值的数量决定。1字节被用于枚举,最大到255个可能的值;2个字节用于枚举,最大到65535 值。

一个SET对象的大小由不同的集合成员的数量决定。如果集合大小是N,对象占据(N+7)/8个字节,四舍五入为1,2,3,4或8 个字节。一个SET最多能有64个成员。

7.3.5 数字类型

MySQL支持所有的ANSI/ISO SQL92的数字类型。这些类型包括准确数字的数据类型(NUMERIC, DECIMAL, INTEGER,和SMALLINT),也包括近似数字的数据类型(FLOAT, REAL,和DOUBLE PRECISION)。关键词INTINTEGER的一个同义词,而关键词DECDECIMAL一个同义词。

NUMERICDECIMAL类型被MySQL实现为同样的类型,这在SQL92标准允许。他们被用于保存值,该值的准确精度是极其重要的值,例如与金钱有关的数据。当声明一个类是这些类型之一时,精度和规模的能被(并且通常是)指定;例如:

salary DECIMAL(9,2) 

在这个例子中,9(precision)代表将被用于存储值的总的小数位数,而2(scale)代表将被用于存储小数点后的位数。因此,在这种情况下,能被存储在salary列中的值的范围是从-9999999.999999999.99。在ANSI/ISO SQL92中,句法DECIMAL(p)等价于DECIMAL(p,0)。同样,句法DECIMAL等价于DECIMAL(p,0),这里实现被允许决定值pMySQL当前不支持DECIMAL/NUMERIC数据类型的这些变种形式的任一种。这一般说来不是一个严重的问题,因为这些类型的主要益处得自于明显地控制精度和规模的能力。

DECIMALNUMERIC值作为字符串存储,而不是作为二进制浮点数,以便保存那些值的小数精度。一个字符用于值的每一位、小数点(如果scale>0)和“-”符号(对于负值)。如果scale是0,DECIMALNUMERIC值不包含小数点或小数部分。

DECIMALNUMERIC值得最大的范围与DOUBLE一样,但是对于一个给定的DECIMALNUMERIC列,实际的范围可由制由给定列的precisionscale限制。当这样的列赋给了小数点后面的位超过指定scale所允许的位的值,该值根据scale四舍五入。当一个DECIMALNUMERIC列被赋给了其大小超过指定(或缺省的)precisionscale隐含的范围的值,MySQL存储表示那个范围的相应的端点值。

作为对ANSI/ISO SQL92标准的扩展,MySQL也支持上表所列的整型类型TINYINT、MEDIUMINT和BIGINT。另一个扩展是MySQL支持可选地指定一个整型值显示的宽度,用括号跟在基本关键词之后(例如,INT(4))。这个可选的宽度指定被用于其宽度小于列指定宽度的值得左填补显示,但是不限制能在列中被存储的值的范围,也不限制值将被显示的位数,其宽度超过列指定的宽度。当与可选的扩展属性ZEROFILL一起使用时,缺省的空格填补用零代替。例如,对于声明为INT(5) ZEROFILL的列,一个为4的值作为00004被检索。注意,如果你在一个整型列存储超过显示宽度的更大值,当MySQL对于某些复杂的联结(join)生成临时表时,你可能会遇到问题,因为在这些情况下,MySQL相信数据确实适合原来的列宽度。

所有的整型类型可以有一个可选(非标准的)属性UNSIGNED。当你想要在列中仅允许正数并且你需要一个稍大一点的列范围,可以使用无符号值。

FLOAT类型被用来标示近似数字的数据类型。ANSI/ISO SQL92标准允许一个可选的精度说明(但不是指数的范围),跟在关键词FLOAT后面的括号内位数。MySQL实现也支持这个可选的精度说明。当关键词FLOAT被用于一个列类型而没有精度说明时,MySQL使用4个字节存储值。一个变种的句法也被支持,在FLOAT关键词后面的括号给出2个数字。用这个选项,第一个数字继续表示在字节计算的值存储需求,而第二个数字指定要被存储的和显示跟随小数点后的位数(就象DECIMALNUMERIC)。当MySQL要求为这样一个列,一个小数点后的小数位超过列指定的值,存储值时,该值被四舍五入,去掉额外的位。

REALDOUBLE PRECISION类型不接受精度说明。作为对 ANSI/ISO SQL92 标准的扩展,MySQL识别出DOUBLE作为DOUBLE PRECISION类型的一个同义词。与REAL精度比用于DOUBLE PRECISION的更小的标准要求相反,MySQL实现了两种,作为8字节双精度浮点值(当运行不是“Ansi模式”时)。为了最大的移植性,近似数字的数据值的存储所需代码应该使用没有精度或小数位数说明的FLOATDOUBLE PRECISION

当要求在数字的列存储超出该列类型允许的范围的值时,MySQL剪切该值到范围内的正确端点值并且存储剪切后的结果值。

例如,一个INT列的范围是-21474836482147483647。如果你试图插入-9999999999到一个INT列中,值被剪切到范围的低部端点,并存储-2147483648。同样,如果你试图插入99999999992147483647被存储。

如果INT列是UNSIGNED,列的范围的大小是相同的,但是它的端点移到了04294967295。如果你试图存储-99999999999999999999,在列被存储的值变为04294967296

对于ALTER TABLELOAD DATA INFILEUPDATE和多行INSERT语句,由于剪切所发生的变换作为“警告”被报告。

7.3.6 日期和时间类型

日期和时间类型是DATETIMEDATETIMESTAMPTIMEYEAR。这些的每一个都有合法值的一个范围,而“零”当你指定确实不合法的值时被使用。注意,MySQL允许你存储某个“不严格地”合法的日期值,例如1999-11-31,原因我们认为它是应用程序的责任来处理日期检查,而不是SQL服务器。为了使日期检查更“快”,MySQL仅检查月份在0-12的范围,天在0-31的范围。上述范围这样被定义是因为MySQL允许你在一个DATEDATETIME列中存储日期,这里的天或月是零。这对存储你不知道准确的日期的一个生日的应用程序来说是极其有用的,在这种情况下,你简单地存储日期象1999-00-001999-01-00。(当然你不能期望从函数如DATE_SUB()DATE_ADD()得到类似以这些日期的正确值)。

当用日期和时间工作时,这里是的一些要记住的一般考虑:

7.3.6.1 Y2K问题和日期类型

MySQL本身Y2K安全的(见1.6 2000年一致性),但是呈交给MySQL的输入值可能不是。一个包含2位年份值的任何输入是由二义性的,因为世纪是未知的。这样的值必须被解释成4位形式,因为MySQL内部使用4位存储年份。

对于DATETIME, DATE, TIMESTAMPYEAR类型,MySQL使用下列规则的解释二义性的年份值:

记得这些规则仅仅提供对于你数据的含义的合理猜测。如果MySQL使用的启发规则不产生正确的值,你应该提供无二义的包含4位年值的输入。

7.3.6.2 DATETIME, DATETIMESTAMP类型

DATETIME, DATETIMESTAMP类型是相关的。本节描述他们的特征,他们是如何类似的而又不同的。

DATETIME类型用在你需要同时包含日期和时间信息的值时。MySQL检索并且以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值,支持的范围是'1000-01-01 00:00:00''9999-12-31 23:59:59'。(“支持”意味着尽管更早的值可能工作,但不能保证他们可以。)

DATE类型用在你仅需要日期值时,没有时间部分。MySQL检索并且以'YYYY-MM-DD'格式显示DATE值,支持的范围是'1000-01-01''9999-12-31'

TIMESTAMP列类型提供一种类型,你可以使用它自动地用当前的日期和时间标记INSERTUPDATE的操作。如果你有多个TIMESTAMP列,只有第一个自动更新。

自动更新第一个TIMESTAMP列在下列任何条件下发生:

除第一个以外的TIMESTAMP列也可以设置到当前的日期和时间,只要将列设为NULL,或NOW()

通过明确地设置希望的值,你可以设置任何TIMESTAMP列为不同于当前日期和时间的值,即使对第一个TIMESTAMP列也是这样。例如,如果,当你创建一个行时,你想要一个TIMESTAMP被设置到当前的日期和时间,但在以后无论何时行被更新时都不改变,你可以使用这个属性:

另一方面,你可能发现,当行被创建并且远离随后的更改时,很容易用一个你用NOW()初始化的DATETIME列。

TIMESTAMP值可以从1970的某时的开始一直到2037年,精度为一秒,其值作为数字显示。

MySQL检索并且显示TIMESTAMP值取决于显示尺寸的格式如下表。“完整”TIMESTAMP格式是14位,但是TIMESTAMP列可以用更短的显示尺寸创造:

列类型 显示格式
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

所有的TIMESTAMP列都有同样的存储大小,不考虑显示尺寸。最常见的显示尺寸是6、8、12、和14。你可以在表创建时间指定一个任意的显示尺寸,但是值0或比14大被强制到14。在从1~13范围的奇数值尺寸被强制为下一个更大的偶数。

使用一个常用的格式集的任何一个,你可以指定DATETIMEDATETIMESTAMP值:

不合法DATETIME, DATETIMESTAMP值被变换到适当类型的“零”值('0000-00-00 00:00:00', '0000-00-00'00000000000000)。

对于包括的日期部分分隔符的指定为字符串的值,不必要为小于10的月或天的值指定2位数字,'1979-6-9''1979-06-09'是一样的。同样, 对于包括的时间部分分隔符的指定为字符串的值,不必为小于10的小时、月或秒指定2位数字,'1979-10-30 1:2:3''1979-10-30 01:02:03'是一样的。

指定为数字应该是6、8、12或14位长。如果数字是8或14位长,它被假定以YYYYMMDDYYYYMMDDHHMMSS格式并且年份由头4位数字给出。如果数字是6或12位长,它被假定是以YYMMDDYYMMDDHHMMSS格式且年份由头2位数字给出。不是这些长度之一的数字通过填补前头的零到最接近的长度来解释。

指定为无分隔符的字符串用它们给定的长度来解释。如果字符串长度是8或14个字符,年份被假定头4个字符给出,否则年份被假定由头2个字符给出。对于字符串中呈现的多个部分,字符串从左到右边被解释,以找出年、月、日、小时、分钟和秒值,这意味着,你不应该使用少于 6 个字符的字符串。例如,如果你指定'9903',认为将代表1999年3月,你会发现MySQL把一个“零”日期插入到你的表中,这是因为年份和月份值9903,但是日期部分丢失(零),因此该值不是一个合法的日期。

TIMESTAMP列使用被指定的值的完整精度的存储合法的值,不考虑显示大小。这有几个含意:

在某种程度上,你可以把一种日期类型的值赋给一个不同的日期类型的对象。然而,这可能值有一些改变或信息的损失:

当指定日期值时,当心某些缺陷:

7.3.6.3 TIME类型

MySQL检索并以'HH:MM:SS'格式显示TIME值(或对大小时值,'HHH:MM:SS'格式)。TIME值的范围可以从'-838:59:59''838:59:59'。小时部分可能很大的的原因是TIME类型不仅可以被使用在表示一天的时间(它必须是不到24个小时),而且用在表示在2个事件之间经过的时间或时间间隔(它可以是比24个小时大些,或甚至是负值)。

你能用多中格式指定TIME值:

对于作为包括一个时间分隔符的字符串被指定的TIME值,不必为小于10的小时、分钟或秒值指定2位数字,'8:3:2''08:03:02'是一样的。

将“短的”TIME值赋值给一个TIME行列是要格外小心。MySQL使用最右位代表秒的假设来解释值。(MySQLTIME值解释为经过的时间,而非作为一天的时间 )例如,你可能想到'11:12''1112'1112意味着'11:12:00'(11点12分),但是MySQL解释他们为'00:11:12'(11分12秒)。同样,'12'12被解释为'00:00:12'

但是超出TIME范围之外的值是样合法的,它被剪切到范围适当的端点值。例如,'-850:00:00''850:00:00'被变换到'-838:59:59''838:59:59'

不合法的TIME值被变换到'00:00:00'。注意,既然'00:00:00'本身是一个合法的TIME值,没有其他方法区分表中存储的一个'00:00:00'值,原来的值是否被指定为'00:00:00'或它是否是不合法的。

7.3.6.4 YEAR类型

YEAR类型是一个 1 字节类型用于表示年份。

MySQL检索并且以YYYY格式显示YEAR值,其范围是19012155

你能用多种格式指定YEAR值:

不合法YEAR值被变换到0000

7.3.7 字符串类型

字符串类型是CHARVARCHARBLOBTEXTENUMSET

7.3.7.1 CHARVARCHAR类型

CHARVARCHAR类型是类似的,但是在他们被存储和检索的方式不同。

一个CHAR列的长度被修正为在你创造表时你所声明的长度。长度可以是1和255之间的任何值。(在MySQL 3.23中,CHAR长度可以是0~255。) 当CHAR值被存储时,他们被用空格在右边填补到指定的长度。当CHAR值被检索时,拖后的空格被删去。

VARCHAR列中的值是变长字符串。你可以声明一个VARCHAR列是在1和255之间的任何长度,就像对CHAR列。然而,与CHAR相反,VARCHAR值只存储所需的字符,外加一个字节记录长度,值不被填补;相反,当值被存储时,拖后的空格被删去。(这个空格删除不同于ANSI SQL规范。)

如果你把一个超过列最大长度的值赋给一个CHARVARCHAR列,值被截断以适合它。

下表显示了两种类型的列的不同,通过演示存储变长字符串值到CHAR(4)VARCHAR(4)列:

CHAR(4) 存储需求 VARCHAR(4) 存储需求
'' ' ' 4 个字节 '' 1 字节
'ab' 'ab ' 4 个字节 'ab' 3 个字节
'abcd' 'abcd' 4 个字节 'abcd' 5 个字节
'abcdefgh' 'abcd' 4 个字节 'abcd' 5 个字节

CHAR(4)VARCHAR(4)列检索的值在每种情况下都是一样的,因为拖后的空格从检索的CHAR列上被删除。

CHARVARCHAR列中存储和比较值是以大小写不区分的方式进行的,除非当表被创建时,BINARY属性被指定。BINARY属性意味着该列的值根据MySQL服务器正在运行的机器的ASCII顺序以大小写区分的方式存储和比较。

BINARY属性是“粘性”的。这意味着,如果标记了BINARY的列用于一个表达式中,整个的表达式作为一个BINARY值被比较。

MySQL在表创建时可以隐含地改变一个CHARVARCHAR列的类型。见7.7.1 隐含的的列说明改变

7.3.7.2 BLOBTEXT类型

一个BLOB是一个能保存可变数量的数据的二进制的大对象。4个BLOB类型TINYBLOBBLOBMEDIUMBLOBLONGBLOB仅仅在他们能保存值的最大长度方面有所不同。见7.3.1 列类型存储需求

4个TEXT类型TINYTEXTTEXTMEDIUMTEXTLONGTEXT对应于4个BLOB类型,并且有同样的最大长度和存储需求。在BLOBTEXT类型之间的唯一差别是对BLOB值的排序和比较以大小写敏感方式执行,而对TEXT值是大小写不敏感的。换句话说,一个TEXT是一个大小写不敏感的BLOB

如果你把一个超过列类型最大长度的值赋给一个BLOBTEXT列,值被截断以适合它。

在大多数方面,你可以认为一个TEXT行列是你所希望大的一个VARCHAR列。同样,你可以认为一个BLOB列是一个VARCHAR BINARY列。差别是:

MyODBC定义BLOBLONGVARBINARYTEXT值为LONGVARCHAR

因为BLOBTEXT值可以是非常长的,当使用他们时,你可能遇到一些限制:

注意,每个BLOBTEXT值内部由一个独立分配的对象表示。这与所有的其他列类型相反,它们是在打开表时,按列被分配一次存储。

7.3.7.3 ENUM类型

一个ENUM是一个字符对象,其值通常从一个在表创建时明确被列举的允许值的一张表中选择。

在下列的某个情形下,值也可以空字符串("")或NULL

每枚举值有一个编号:

例如,指定为ENUM("one", "two", "three")的列可以有显示在下面的值的任何一个。每个值的编号也被显示:

编号
NULL NULL
"" 0
"one" 1
"two" 2
"three" 3

枚举可以有最大65535个成员。

当你把值赋给一个ENUM列时,字母的大小写是无关紧要的。然而,以后从列中检索的值大小写匹配在表创建时用来指定允许值的值的大小写。

如果你在一个数字的上下文环境中检索一个ENUM,列值的编号被返回。如果你存储一个数字到一个ENUM中,数字被当作一个标号,并且存储的值是该编号的枚举成员。

ENUM值根据列说明列举的枚举成员的次序被排序。(换句话说,ENUM值根据他们的编号数字被排序) 例如,对ENUM("a", "b"),"a"排在"b"前面,但是对ENUM("b", "a"),"b"排在"a"前面。空字符串排序非空字符串之前,并且NULL排在所有其他枚举值之前。

如果你想要得到一个ENUM列的所有可能的值,你应该使用:SHOW COLUMNS FROM table_name LIKE enum_column_name并且分析在第二列的ENUM定义。

7.3.7.4 SET类型

一个SET是可以有零或多个值的一个字符串对象,其每一个必须从表创建造被指定了的允许值的一张列表中被选择。由多个集合成员组成的SET列通过由由逗号分隔(“,”)的成员被指定,其推论是该SET成员值不能包含逗号本身。

例如, 一个指定为SET("one", "two") NOT NULL的列可以有这些值的任何一个:

"" 
"one" 
"two" 
"one,two"

一个SET能有最多64个不同的成员。 

MySQL用数字值存储SET值,存储值的低阶位对应于第一个集合成员。如果你在数字上下文中检索一个SET值,检索的值把位设置位对应组成列值的集合成员。如果一个数字被存储进一个SET列,在数字的二进制表示中设置的位决定了在列中的集合成员。假定一个列被指定为SET("a","b","c","d"),那么成员有下列位值:

SET 成员 十进制的值 二进制的值
a 1 0001
b 2 0010
c 4 0100
d 8 1000

如果你给该列赋值9,即二进制的1001,这样第一个和第四个SET值成员"a""d"被选择并且结果值是"a,d"

对于包含超过一个SET成员的值,当你插入值时,无所谓以什么顺序列举值,也无所谓给定的值列举了多少次。当以后检索值时,在值中的每个成员将出现一次,根据他们在表创建时被指定的顺序列出成员。例如,如果列指定为SET("a","b","c","d"),那么"a,d""d,a""d,a,a,d,d"在检索时将均作为"a,d"出现。

SET值以数字次序被排序。NULL指排在非NULL SET值之前。

通常,你使用LIKE操作符或FIND_IN_SET()函数执行在一个SET上的一个SELECT

mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

但是下列也会工作:

mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

这些语句的第一个语句寻找一个精确的匹配。第二个寻找包含第一个集合成员的值。

如果你想要得到一个SET列的所有可能的值,你应该使用:SHOW COLUMNS FROM table_name LIKE set_column_name并且分析在第二列的SET定义。

7.3.8 为列选择正确的类型

为了最有效地使用存储空间,试着在所有的情况下使用最精确的类型。例如,如果一个整数列被用于在之间199999的值, MEDIUMINT UNSIGNED是最好的类型。

货币值的精确表示是一个常见的问题。在MySQL,你应该使用DECIMAL类型,它作为一个字符串被存储,不会发生精确性的损失。如果精确性不是太重要,DOUBLE类型也是足够好的。

对高精度,你总是能变换到以一个BIGINT存储的定点类型。这允许你用整数做所有的计算,并且仅在必要时将结果转换回浮点值。见10.6 选择一个表类型

7.3.9 列索引

所有的MySQL列类型能被索引。在相关的列上的使用索引是改进SELECT操作性能的最好方法。

一个表最多可有16个索引。最大索引长度是256个字节,尽管这可以在编译MySQL时被改变。

对于CHARVARCHAR列,你可以索引列的前缀。这更快并且比索引整个列需要较少的磁盘空间。在CREATE TABLE语句中索引列前缀的语法看起来像这样:

KEY index_name (col_name(length)) 

下面的例子为name列的头10个字符创建一个索引:

mysql> CREATE TABLE test (
           name CHAR(200) NOT NULL,
           KEY index_name (name(10)));

对于BLOBTEXT列,你必须索引列的前缀,你不能索引列的全部。

7.3.10 多列索引

MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。(在CHARVARCHAR列上,你也可以使用列的前缀作为一个索引的部分)。

一个多重列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。

当你为在一个WHERE子句索引的第一列指定已知的数量时,MySQL以这种方式使用多重列索引使得查询非常快速,即使你不为其他列指定值。

假定一张表使用下列说明创建:

mysql> CREATE TABLE test (
           id INT NOT NULL,
           last_name CHAR(30) NOT NULL,
           first_name CHAR(30) NOT NULL,
           PRIMARY KEY (id),
           INDEX name (last_name,first_name));

那么索引name是一个在last_namefirst_name上的索引,这个索引将被用于在last_namelast_namefirst_name的一个已知范围内指定值的查询,因此,name索引将使用在下列查询中:

mysql> SELECT * FROM test WHERE last_name="Widenius";

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          AND first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          AND (first_name="Michael" OR first_name="Monty");

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          AND first_name >="M" AND first_name < "N";

然而,name索引将不用在下列询问中:

mysql> SELECT * FROM test WHERE first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
                          OR first_name="Michael";

关于MySQL使用索引改进性能的方式的更多的信息,见10.4 使用MySQL索引

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

为了跟容易地使用为其他供应商的SQL实现编写的代码,下表显示了MySQL映射的列类型。这些映射使得从其他数据库引擎移动表定义到MySQL更容易:

其他供应商类型 MySQL类型
BINARY(NUM) CHAR(NUM) BINARY
CHAR VARYING(NUM) VARCHAR(NUM)
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
MIDDLEINT MEDIUMINT
VARBINARY(NUM) VARCHAR(NUM) BINARY

列类型映射发生在表创建时。如果你用其他供应商使用的类型创建表,那么发出一个DESCRIBE tbl_name语句,MySQL使用等价的MySQL类型报告表结构。

7.4 用在SELECTWHERE子句中的函数

在一个SQL语句中的select_expressionwhere_definition可由使用下面描述的函数的任何表达式组成。

包含NULL的一个表达式总是产生一个NULL值,否则除非表达式所包含的操作符和函数在文档中说明。

注意:在一个函数名和跟随它的括号之间不许没有空格。这帮助MySQL分析器区分函数调用和具有相同名字的对表或列的引用,尽管允许在参数周围有空格。

为了简洁,例子以缩写形式显示从mysql程序输出。因此:

mysql> select MOD(29,9); 
1 rows in set (0.00 sec) 

+-----------+
| mod(29,9) |
+-----------+
|         2 |
+-----------+

被显示为这样:

mysql> select MOD(29,9);
        -> 2

7.4.1 分组函数

( ... )
括号。使用它们来强制在一个表达式的计算顺序。
mysql> select 1+2*3;
        -> 7
mysql> select (1+2)*3;
        -> 9

7.4.2 常用的算术操作

一般的算术操作符是可用的。注意在-+*情况下,如果两个参数是整数,结果用BIGINT(64位)精度计算!  

+
加法
mysql> select 3+5;
        -> 8
-
减法
mysql> select 3-5;
        -> -2  
*
乘法
mysql> select 3*5;
        -> 15
mysql> select 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
mysql> select 18014398509481984*18014398509481984;
        -> 0

最后一个表达式的结果是不正确的,因为整数乘积的结果超过用BIGINT计算的64位范围。

/
除法
mysql> select 3/5;
        -> 0.60

被零除产生一个NULL结果:

mysql> select 102/(1-1);
        -> NULL

一个除法用BIGINT算术计算,只要在它的结果被转换到一个整数的上下文中执行!

7.4.3 位函数

MySQL为位操作使用BIGINT(64位)算法,因此这些操作符有最大64位的一个范围。

|
位或
mysql> select 29 | 15;
        -> 31
&
位与
mysql> select 29 & 15;
        -> 13
 
<<
左移位一个长(BIGINT)数字。
mysql> select 1 << 2
        -> 4
 
>>
右移位一个长(BIGINT)数字。
mysql> select 4 >> 2
        -> 1 
~
颠倒所有的位。
mysql> select 5 & ~1
        -> 4
 
BIT_COUNT(N)
返回在参数N设定的位的数量。
mysql> select BIT_COUNT(29);
        -> 4
  

7.4.4 逻辑运算

所有的逻辑函数返回1(TRUE)或0(FALSE)。

NOT
!
逻辑非。如果参数是0,返回1,否则返回0。例外: NOT NULL返回NULL
mysql> select NOT 1;
        -> 0
mysql> select NOT NULL;
        -> NULL
mysql> select ! (1+1);
        -> 0
mysql> select ! 1+1;
        -> 1
最后的例子返回1,因为表达式作为(!1)+1计算。   
OR
 
||
逻辑或。如果任何一个参数不是0并且不NULL,返回1
mysql> select 1 || 0;
        -> 1
mysql> select 0 || 0;
        -> 0
mysql> select 1 || NULL;
        -> 1
 
AND
 
&&
逻辑与。如果任何一个参数是0NULL,返回0,否则返回1
mysql> select 1 && NULL;
        -> 0
mysql> select 1 && 0;
        -> 0
   

7.4.5 比较运算符

比较操作得出值1(TRUE)、0(FALSE)或NULL等结果。这些函数工作运用在数字和字符串上。当需要时,字符串自动地被变换到数字且数字到字符串(如在Perl)。

MySQL使用下列规则执行比较:

缺省地,字符串使用当前的字符集以大小写敏感的方式进行(缺省为ISO-8859-1 Latin1,它对英语运用得很出色)。

下面的例子演示了对于比较操作字符串到数字的转换:

mysql> SELECT 1 > '6x';
         -> 0
mysql> SELECT 7 > '6x';
         -> 1
mysql> SELECT 0 > 'x6';
         -> 0
mysql> SELECT 0 = 'x6';
         -> 1
=
等于
mysql> select 1 = 0;
        -> 0
mysql> select '0' = 0;
        -> 1
mysql> select '0.0' = 0;
        -> 1
mysql> select '0.01' = 0;
        -> 0
mysql> select '.01' = 0.01;
        -> 1
    
<>
 
!=
不等于
mysql> select '.01' <> '0.01';
        -> 1
mysql> select .01 <> '0.01';
        -> 0
mysql> select 'zapp' <> 'zappp';
        -> 1
<=
小于或等于
mysql> select 0.1 <= 2;
        -> 1
 
<
小于
mysql> select 2 <= 2;
        -> 1
  
>=
大于或等于
mysql> select 2 >= 2;
        -> 1 
>
大于
mysql> select 2 > 2;
        -> 0
<=>
安全等于Null
mysql> select 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1 1 0
  
IS NULL
 
IS NOT NULL
测试值是否是或不是NULL
mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL:
        -> 0 0 1
mysql> select 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
 
expr BETWEEN min AND max
如果expr对大于或等于minexpr是小于或等于maxBETWEEN返回1,否则它返回0。如果所有的参数类型是一样得,这等价于表达式(min <= expr AND expr <= max)。第一个参数(expr)决定比较如何被执行。如果expr是一个大小写不敏感的字符串表达式,进行一个大小写不敏感的字符串比较。如果expr是一个大小写敏感的字符串表达式,进行一个大小写敏感的字符串比较。如果expr是一个整数表达式,进行整数比较。否则,进行一个浮点(实数)比较。
mysql> select 1 BETWEEN 2 AND 3;
        -> 0
mysql> select 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> select 2 BETWEEN 2 AND '3';
        -> 1
mysql> select 2 BETWEEN 2 AND 'x-3';
        -> 0
expr IN (value,...)
如果expr是在IN表中的任何值,返回1,否则返回0。如果所有的值是常数,那么所有的值根据expr类型被计算和排序,然后项目的搜索是用二进制的搜索完成。这意味着如果IN值表全部由常数组成,IN是很快的。如果expr是一个大小写敏感的字符串表达式,字符串比较以大小写敏感方式执行。
mysql> select 2 IN (0,3,5,'wefwf');
        -> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
        -> 1 
expr NOT IN (value,...)
NOT (expr IN (value,...))相同。
ISNULL(expr)
如果exprNULLISNULL()返回1,否则它返回0
mysql> select ISNULL(1+1);
        -> 0
mysql> select ISNULL(1/0);
        -> 1

注意,使用=NULL的值比较总为假!

COALESCE(list)
回来list中第一个非NULL的单元。
mysql> select COALESCE(NULL,1);
        -> 1
mysql> select COALESCE(NULL,NULL,NULL);
        -> NULL
INTERVAL(N,N1,N2,N3,...)
如果N< N1,返回0,如果N< N2,返回1等等。所有的参数被当作整数。为了函数能正确地工作,它要求N1<N2<N3< ...<Nn。这是因为使用二进制搜索(很快)。
mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);
        -> 0  

7.4.6 字符串比较函数

通常,如果在字符串比较中的任何表达式是区分大小写的,比较以大小写敏感的方式执行。

expr LIKE pat [ESCAPE 'escape-char']
使用SQL的简单的正规表达式比较的模式匹配。返回1(TRUE)或0(FALSE)。用LIKE,你可以在模式中使用下列2个通配符字符:
% 匹配任何数目的字符,甚至零个字符
_ 精确匹配一个字符
mysql> select 'David!' LIKE 'David_';
        -> 1
mysql> select 'David!' LIKE '%D%v%';
        -> 1

为了测试一个通配符的文字实例,用转义字符的加在字符前面。如果你不指定ESCAPE字符,假定为“\”

\% 匹配一%字符
\_ 匹配一_字符
mysql> select 'David!' LIKE 'David\_';
        -> 0
mysql> select 'David_' LIKE 'David\_';
        -> 1

为了指定一个不同的转义字符,使用ESCAPE子句: 
mysql> select 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

LIKE允许用在数字的表达式上!(这是MySQL对ANSI SQL LIKE的一个扩充。)

mysql> select 10 LIKE '1%';
        -> 1

注意:因为MySQL在字符串中使用C转义语法(例如,“\n”),你必须在你的LIKE字符串中重复任何“\”。例如,为了查找“\n”,指定它为“ \\n”,为了查找“\”,指定它为“\\\\”(反斜线被分析器剥去一次,另一次是在模式匹配完成时,留下一条单独的反斜线被匹配)。

expr NOT LIKE pat [ESCAPE 'escape-char']
NOT (expr LIKE pat [ESCAPE 'escape-char'])相同。
expr REGEXP pat
 
expr RLIKE pat
执行一个字符串表达式expr对一个模式pat的模式匹配。模式可以是一个扩充的正则表达式。见MySQL 正则表达式句法的 H 描述.如果expr匹配pat,返回1,否则返回0RLIKEREGEXP的一个同义词,提供了与mSQL的兼容性。注意:因为MySQL在字符串中使用C转义语法(例如,“\n”), 你必须在你的REGEXP字符串重复任何“\”。在MySQL3.23.4中,REGEXP对于正常的(不是二进制)字符串是忽略大小写。
mysql> select 'Monty!' REGEXP 'm%y%%';
        -> 0
mysql> select 'Monty!' REGEXP '.*';
        -> 1
mysql> select 'new*\n*line' REGEXP 'new\\*.\\*line';
        -> 1
mysql> select "a" REGEXP "A", "a" REGEXP BINARY "A";
        -> 1  0
当决定一个字符的类型时,REGEXPRLIKE使用当前的字符集(缺省为ISO-8859-1 Latin1)。
expr NOT REGEXP pat
 
expr NOT RLIKE pat
NOT (expr REGEXP pat)相同。
STRCMP(expr1,expr2)
如果字符串相同,STRCMP()回来0,如果第一参数根据当前的排序次序小于第二个,返回-1,否则返回1
mysql> select STRCMP('text', 'text2');
        -> -1
mysql> select STRCMP('text2', 'text');
        -> 1
mysql> select STRCMP('text', 'text');
        -> 0
 

7.4.7 类型转换运算符

BINARY
BINARY操作符强制跟随它后面的字符串为一个二进制字符串。即使列没被定义为BINARYBLOB,这是一个强制列比较区分大小写的简易方法。
mysql> select "a" = "A";
        -> 1
mysql> select BINARY "a" = "A";
        -> 0

BINARYMySQL 3.23.0中被引入。

7.4.8 控制流函数

IFNULL(expr1,expr2)
如果expr1不是NULLIFNULL()返回expr1,否则它返回expr2IFNULL()返回一个数字或字符串值,取决于它被使用的上下文环境。
mysql> select IFNULL(1,0);
        -> 1
mysql> select IFNULL(0,10);
        -> 0
mysql> select IFNULL(1/0,10);
        -> 10
mysql> select IFNULL(1/0,'yes');
        -> 'yes'
 
IF(expr1,expr2,expr3)
如果expr1是TRUE(expr1<>0expr1<>NULL),那么IF()返回expr2,否则它返回expr3IF()返回一个数字或字符串值,取决于它被使用的上下文。
mysql> select IF(1>2,2,3);
        -> 3
mysql> select IF(1<2,'yes','no');
        -> 'yes'
mysql> select IF(strcmp('test','test1'),'yes','no');
        -> 'no'

expr1作为整数值被计算,它意味着如果你正在测试浮点或字符串值,你应该使用一个比较操作来做。

mysql> select IF(0.1,1,0);
        -> 0
mysql> select IF(0.1<>0,1,0);
        -> 1

在上面的第一种情况中,IF(0.1)返回0,因为0.1被变换到整数值, 导致测试IF(0)。这可能不是你期望的。在第二种情况中,比较测试原来的浮点值看它是否是非零,比较的结果被用作一个整数。

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
 
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
第一个版本返回result,其中value=compare-value。第二个版本中如果第一个条件为真,返回result。如果没有匹配的result值,那么结果在ELSE后的result被返回。如果没有ELSE部分,那么NULL被返回。
mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;
       -> "one"
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
       -> "true"
mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END;
       -> NULL

7.4.9 数学函数

所有的数学函数在一个出错的情况下返回NULL

-
单目减。改变参数的符号。
mysql> select - 2;
注意,如果这个操作符与一个BIGINT使用,返回值是一个BIGINT!这意味着你应该避免在整数上使用-,那可能有值-2^63
ABS(X)
返回X的绝对值。
mysql> select ABS(2);
        -> 2
mysql> select ABS(-32);
        -> 32

该功能可安全用于BIGINT值。

SIGN(X)
返回参数的符号,为-101,取决于X是否是负数、零或正数。
mysql> select SIGN(-32);
        -> -1
mysql> select SIGN(0);
        -> 0
mysql> select SIGN(234);
        -> 1
   
MOD(N,M)
 
%
模 (类似C中的%操作符)。返回NM除的余数。
mysql> select MOD(234, 10);
        -> 4
mysql> select 253 % 7;
        -> 1
mysql> select MOD(29,9);
        -> 2
这个函数可安全用于BIGINT值。  
FLOOR(X)
返回不大于X的最大整数值。

    
mysql> select FLOOR(1.23);
        -> 1
mysql> select FLOOR(-1.23);
        -> -2
注意返回值被变换为一个BIGINT 
CEILING(X)
返回不小于X的最小整数值。
mysql> select CEILING(1.23);
        -> 2
mysql> select CEILING(-1.23);
        -> -1

注意返回值被变换为一个BIGINT

ROUND(X)
返回参数X的四舍五入的一个整数。
mysql> select ROUND(-1.23);
        -> -1
mysql> select ROUND(-1.58);
        -> -2
mysql> select ROUND(1.58);
        -> 2
 

注意返回值被变换为一个BIGINT!

ROUND(X,D)
返回参数X的四舍五入的有D为小数的一个数字。如果D0,结果将没有小数点或小数部分。
mysql> select ROUND(1.298, 1);
        -> 1.3
mysql> select ROUND(1.298, 0);
        -> 1

注意返回值被变换为一个BIGINT!

EXP(X)
返回值e(自然对数的底)的X次方。
mysql> select EXP(2);
        -> 7.389056
mysql> select EXP(-2);
        -> 0.135335
 
LOG(X)
返回X的自然对数。
mysql> select LOG(2);
        -> 0.693147
mysql> select LOG(-2);
        -> NULL

如果你想要一个数字X的任意底B的对数,使用公式LOG(X)/LOG(B)

LOG10(X)
返回X的以10为底的对数。
mysql> select LOG10(2);
        -> 0.301030
mysql> select LOG10(100);
        -> 2.000000
mysql> select LOG10(-100);
        -> NULL
POW(X,Y)
 
POWER(X,Y)
返回值XY次幂。
mysql> select POW(2,2);
        -> 4.000000
mysql> select POW(2,-2);
        -> 0.250000
SQRT(X)
返回非负数X的平方根。
mysql> select SQRT(4);
        -> 2.000000
mysql> select SQRT(20);
        -> 4.472136
 
PI()
返回PI的值(圆周率)。
mysql> select PI();
        -> 3.141593
COS(X)
返回X的余弦, 在这里X以弧度给出。
mysql> select COS(PI());
        -> -1.000000
 
SIN(X)
返回X的正弦值,在此X以弧度给出。
mysql> select SIN(PI());
        -> 0.000000
 
TAN(X)
返回X的正切值,在此X以弧度给出。
mysql> select TAN(PI()+1);
        -> 1.557408
 
ACOS(X)
返回X反余弦,即其余弦值是X。如果X不在-11的范围,返回NULL
mysql> select ACOS(1);
        -> 0.000000
mysql> select ACOS(1.0001);
        -> NULL
mysql> select ACOS(0);
        -> 1.570796
 
ASIN(X)
返回X反正弦值,即其正弦值是XL如果X不在-11的范围,返回NULL
mysql> select ASIN(0.2);
        -> 0.201358
mysql> select ASIN('foo');
        -> 0.000000
 
ATAN(X)
返回X的反正切值,即其正切值是X
mysql> select ATAN(2);
        -> 1.107149
mysql> select ATAN(-2);
        -> -1.107149
ATAN2(X,Y)
返回2个变量XY的反正切。它类似于计算Y/X的反正切,除了两个参数的符号被用来决定结果的象限。
mysql> select ATAN(-2,2);
        -> -0.785398
mysql> select ATAN(PI(),0);
        -> 1.570796 
COT(X)
返回X的余切。
mysql> select COT(12);
        -> -1.57267341
mysql> select COT(0);
        -> NULL
RAND()
 
RAND(N)
返回在范围01.0内的随机浮点值。如果一个整数参数N被指定,它被用作种子值。
mysql> select RAND();
        -> 0.5925
mysql> select RAND(20);
        -> 0.1811
mysql> select RAND(20);
        -> 0.1811
mysql> select RAND();
        -> 0.2079
mysql> select RAND();
        -> 0.7888

你不能在一个ORDER BY子句用RAND()值使用列,因为ORDER BY将重复计算列多次。然而在MySQL3.23中,你可以做: SELECT * FROM table_name ORDER BY RAND(),这是有利于得到一个来自SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000的集合的随机样本。注意在一个WHERE子句里的一个RAND()将在每次WHERE被执行时重新评估。

LEAST(X,Y,...)
有2和2个以上的参数,返回最小(最小值)的参数。参数使用下列规则进行比较:
mysql> select LEAST(2,0);
        -> 0
mysql> select LEAST(34.0,3.0,5.0,767.0);
        -> 3.0
mysql> select LEAST("B","A","C");
        -> "A"

MySQL 3.22.5以前的版本,你可以使用MIN()而不是LEAST

GREATEST(X,Y,...)
返回最大(最大值)的参数。参数使用与LEAST一样的规则进行比较。
mysql> select GREATEST(2,0);
        -> 2
mysql> select GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> select GREATEST("B","A","C");
        -> "C"
MySQL在 3.22.5 以前的版本, 你能使用MAX()而不是GREATEST.  
DEGREES(X)
返回参数X,从弧度变换为角度。
mysql> select DEGREES(PI());
        -> 180.000000
RADIANS(X)
返回参数X,从角度变换为弧度。
mysql> select RADIANS(90);
        -> 1.570796
TRUNCATE(X,D)
返回数字X,截断为D位小数。如果D0,结果将没有小数点或小数部分。
mysql> select TRUNCATE(1.223,1);
        -> 1.2
mysql> select TRUNCATE(1.999,1);
        -> 1.9
mysql> select TRUNCATE(1.999,0);
        -> 1
 

7.4.10 字符串函数

如果结果的长度大于服务器参数max_allowed_packet,字符串值函数返回NULL。见10.2.3 调节服务器参数

对于针对字符串位置的操作,第一个位置被标记为1。

ASCII(str)
返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果strNULL,返回NULL
mysql> select ASCII('2');
        -> 50
mysql> select ASCII(2);
        -> 50
mysql> select ASCII('dx');
        -> 100

也可参见ORD()函数。

ORD(str)
如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。
mysql> select ORD('2');
        -> 50
 
CONV(N,from_base,to_base)
在不同的数字基之间变换数字。返回数字N的字符串数字,从from_base基变换为to_base基,如果任何参数是NULL,返回NULL。参数N解释为一个整数,但是可以指定为一个整数或一个字符串。最小基是2且最大的基是36。如果to_base是一个负数,N被认为是一个有符号数,否则,N被当作无符号数。 CONV以64位点精度工作。
mysql> select CONV("a",16,2);
        -> '1010'
mysql> select CONV("6E",18,8);
        -> '172'
mysql> select CONV(-17,10,-18);
        -> '-H'
mysql> select CONV(10+"10"+'10'+0xa,10,10);
        -> '40'
 
BIN(N)
返回二进制值N的一个字符串表示,在此N是一个长整数(BIGINT)数字,这等价于CONV(N,10,2)。如果NNULL,返回NULL
mysql> select BIN(12);
        -> '1100'
OCT(N)
返回八进制值N的一个字符串的表示,在此N是一个长整型数字,这等价于CONV(N,10,8)。如果N是NULL,返回NULL
mysql> select OCT(12);
        -> '14'
 
HEX(N)
返回十六进制值N一个字符串的表示,在此N是一个长整型(BIGINT)数字,这等价于CONV(N,10,16)。如果NNULL,返回NULL
mysql> select HEX(255);
        -> 'FF'
 
CHAR(N,...)
CHAR()将参数解释为整数并且返回由这些整数的ASCII代码字符组成的一个字符串。NULL值被跳过。
mysql> select CHAR(77,121,83,81,'76');
        -> 'MySQL'
mysql> select CHAR(77,77.3,'77.3');
        -> 'MMM'
 
CONCAT(str1,str2,...)
返回来自于参数连结的字符串。如果任何参数是NULL,返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
mysql> select CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
        -> NULL
mysql> select CONCAT(14.3);
        -> '14.3'
LENGTH(str)
 
OCTET_LENGTH(str)
 
CHAR_LENGTH(str)
 
CHARACTER_LENGTH(str)
返回字符串str的长度。
mysql> select LENGTH('text');
        -> 4
mysql> select OCTET_LENGTH('text');
        -> 4

注意,对于多字节字符,其CHAR_LENGTH()仅计算一次。

LOCATE(substr,str)
 
POSITION(substr IN str)
返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0.
mysql> select LOCATE('bar', 'foobarbar');
        -> 4
mysql> select LOCATE('xbar', 'foobar');
        -> 0
该函数是多字节可靠的。  
LOCATE(substr,str,pos)
返回子串substr在字符串str第一个出现的位置,从位置pos开始。如果substr不是在str里面,返回0
mysql> select LOCATE('bar', 'foobarbar',5);
        -> 7

这函数是多字节可靠的。

INSTR(str,substr)
返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同,除了参数被颠倒。
mysql> select INSTR('foobarbar', 'bar');
        -> 4
mysql> select INSTR('xbar', 'foobar');
        -> 0

这函数是多字节可靠的。

LPAD(str,len,padstr)
返回字符串str,左面用字符串padstr填补直到strlen个字符长。
mysql> select LPAD('hi',4,'??');
        -> '??hi'
 
RPAD(str,len,padstr)
返回字符串str,右面用字符串padstr填补直到strlen个字符长。  
mysql> select RPAD('hi',5,'?');
        -> 'hi???'
LEFT(str,len)
返回字符串str的最左面len个字符。
mysql> select LEFT('foobarbar', 5);
        -> 'fooba'

该函数是多字节可靠的。

RIGHT(str,len)
返回字符串str的最右面len个字符
mysql> select RIGHT('foobarbar', 4);
        -> 'rbar'

该函数是多字节可靠的。

SUBSTRING(str,pos,len)
 
SUBSTRING(str FROM pos FOR len)
 
MID(str,pos,len)
从字符串str返回一个len个字符的子串,从位置pos开始。使用FROM的变种形式是ANSI SQL92语法。
mysql> select SUBSTRING('Quadratically',5,6);
        -> 'ratica'

该函数是多字节可靠的。

SUBSTRING(str,pos)
 
SUBSTRING(str FROM pos)
从字符串str的起始位置pos返回一个子串。
mysql> select SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> select SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'

该函数是多字节可靠的。

SUBSTRING_INDEX(str,delim,count)
返回从字符串str的第count个出现分隔符delim之后的子串。如果count是正数,返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数,返回最后的分隔符到右边的所有字符(从右边数)。
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

该函数对多字节是可靠的。

LTRIM(str)
返回删除了其前置空格字符的字符串str
mysql> select LTRIM('  barbar');
        -> 'barbar'
RTRIM(str)
返回删除了其拖后空格字符的字符串str
mysql> select RTRIM('barbar   ');
        -> 'barbar'
该函数对多字节是可靠的。  
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
返回字符串str,其所有remstr前缀或后缀被删除了。如果没有修饰符BOTHLEADINGTRAILING给出,BOTH被假定。如果remstr没被指定,空格被删除。
mysql> select TRIM('  bar   ');
        -> 'bar'
mysql> select TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> select TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> select TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'

该函数对多字节是可靠的。

SOUNDEX(str)
返回str的一个同音字符串。听起来“大致相同”的2个字符串应该有相同的同音字符串。一个“标准”的同音字符串长是4个字符,但是SOUNDEX()函数返回一个任意长的字符串。你可以在结果上使用SUBSTRING()得到一个“标准”的 同音串。所有非数字字母字符在给定的字符串中被忽略。所有在A-Z之外的字符国际字母被当作元音。
mysql> select SOUNDEX('Hello');
        -> 'H400'
mysql> select SOUNDEX('Quadratically');
        -> 'Q36324'
 
SPACE(N)
返回由N个空格字符组成的一个字符串。
mysql> select SPACE(6);
        -> '      '
 
REPLACE(str,from_str,to_str)
返回字符串str,其字符串from_str的所有出现由字符串to_str代替。
mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

该函数对多字节是可靠的。

REPEAT(str,count)
返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0,返回一个空字符串。如果strcountNULL,返回NULL
mysql> select REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'
 
REVERSE(str)
返回颠倒字符顺序的字符串str
mysql> select REVERSE('abc');
        -> 'cba'

该函数对多字节可靠的。

INSERT(str,pos,len,newstr)
返回字符串str,在位置pos起始的子串且len个字符长得子串由字符串newstr代替。
mysql> select INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'

该函数对多字节是可靠的。

ELT(N,str1,str2,str3,...)
如果N= 1,返回str1,如果N= 2,返回str2,等等。如果N小于1或大于参数个数,返回NULLELT()FIELD()反运算。
mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'
FIELD(str,str1,str2,str3,...)
返回strstr1, str2, str3, ...清单的索引。如果str没找到,返回0FIELD()ELT()反运算。
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 2
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 0
FIND_IN_SET(str,strlist)
如果字符串str在由N子串组成的表strlist之中,返回一个1N的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为SET的列,FIND_IN_SET()函数被优化而使用位运算!如果str不是在strlist里面或如果strlist是空字符串,返回0。如果任何一个参数是NULL,返回NULL。如果第一个参数包含一个“,”,该函数将工作不正常。
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2
 
MAKE_SET(bits,str1,str2,...)
返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串),由相应的位在bits集合中的的字符串组成。str1对应于位0,str2对应位1,等等。在str1, str2, ...中的NULL串不添加到结果中。
mysql> SELECT MAKE_SET(1,'a','b','c');
        -> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
        -> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
        -> ''
EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
返回一个字符串,在这里对于在“bits”中设定每一位,你得到一个“on”字符串,并且对于每个复位(reset)的位,你得到一个“off”字符串。每个字符串用“separator”分隔(缺省“,”),并且只有“bits”的“number_of_bits” (缺省64)位被使用。
mysql> select EXPORT_SET(5,'Y','N',',',4)
        -> Y,N,Y,N 
LCASE(str)
 
LOWER(str)
返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成小写。该函数对多字节是可靠的。
mysql> select LCASE('QUADRATICALLY');
        -> 'quadratically'
 
UCASE(str)
 
UPPER(str)
返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成大写。该函数对多字节是可靠的。
mysql> select UCASE('Hej');
        -> 'HEJ'

该函数对多字节是可靠的。

LOAD_FILE(file_name)
读入文件并且作为一个字符串返回文件内容。文件必须在服务器上,你必须指定到文件的完整路径名,而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出,函数返回NULL
mysql> UPDATE table_name
           SET blob_column=LOAD_FILE("/tmp/picture")
           WHERE id=1;

MySQL必要时自动变换数字为字符串,并且反过来也如此:

mysql> SELECT 1+"1";
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

如果你想要明确地变换一个数字到一个字符串,把它作为参数传递到CONCAT()

如果字符串函数提供一个二进制字符串作为参数,结果字符串也是一个二进制字符串。被变换到一个字符串的数字被当作是一个二进制字符串。这仅影响比较。

7.4.11 日期和时间函数

对于每个类型拥有的值范围以及并且指定日期何时间值的有效格式的描述见7.3.6 日期和时间类型

这里是一个使用日期函数的例子。下面的查询选择了所有记录,其date_col的值是在最后30天以内:

mysql> SELECT something FROM table
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
返回日期date的星期索引(1=星期天,2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。
mysql> select DAYOFWEEK('1998-02-03');
        -> 3
 
WEEKDAY(date)
返回date的星期索引(0=星期一,1=星期二, ……6= 星期天)。
mysql> select WEEKDAY('1997-10-04 22:23:00');
        -> 5
mysql> select WEEKDAY('1997-11-05');
        -> 2
 
DAYOFMONTH(date)
返回date的月份中日期,在131范围内。
mysql> select DAYOFMONTH('1998-02-03');
        -> 3
DAYOFYEAR(date)
返回date在一年中的日数, 在1366范围内。
mysql> select DAYOFYEAR('1998-02-03');
        -> 34
MONTH(date)
返回date的月份,范围112
mysql> select MONTH('1998-02-03');
        -> 2
 
DAYNAME(date)
返回date的星期名字。
mysql> select DAYNAME("1998-02-05");
        -> 'Thursday'
MONTHNAME(date)
返回date的月份名字。
mysql> select MONTHNAME("1998-02-05");
        -> 'February'
 
QUARTER(date)
返回date一年中的季度,范围14
mysql> select QUARTER('98-04-01');
        -> 2
 
WEEK(date)
 
WEEK(date,first)
对于星期天是一周的第一天的地方,有一个单个参数,返回date的周数,范围在052。2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。如果第二个参数是0,星期从星期天开始,如果第二个参数是1,从星期一开始。
mysql> select WEEK('1998-02-20');
        -> 7
mysql> select WEEK('1998-02-20',0);
        -> 7
mysql> select WEEK('1998-02-20',1);
        -> 8
YEAR(date)
返回date的年份,范围在10009999
mysql> select YEAR('98-02-03');
        -> 1998
HOUR(time)
返回time的小时,范围是023
mysql> select HOUR('10:05:03');
        -> 10
MINUTE(time)
返回time的分钟,范围是059
mysql> select MINUTE('98-02-03 10:05:03');
        -> 5
SECOND(time)
回来time的秒数,范围是059
mysql> select SECOND('10:05:03');
        -> 3
PERIOD_ADD(P,N)
增加N个月到阶段P(以格式YYMMYYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。
mysql> select PERIOD_ADD(9801,2);
        -> 199803
PERIOD_DIFF(P1,P2)
返回在时期P1P2之间月数,P1P2应该以格式YYMMYYYYMM。注意,时期参数P1P2不是日期值。
mysql> select PERIOD_DIFF(9802,199703);
        -> 11
    
DATE_ADD(date,INTERVAL expr type)
 
DATE_SUB(date,INTERVAL expr type)
 
ADDDATE(date,INTERVAL expr type)
 
SUBDATE(date,INTERVAL expr type)
这些功能执行日期运算。对于MySQL 3.22,他们是新的。ADDDATE()SUBDATE()DATE_ADD()DATE_SUB()的同义词。在MySQL 3.23中,你可以使用+-而不是DATE_ADD()DATE_SUB()。(见例子)date是一个指定开始日期的DATETIMEDATE值,expr是指定加到开始日期或从开始日期减去的间隔值一个表达式,expr是一个字符串;它可以以一个“-”开始表示负间隔。type是一个关键词,指明表达式应该如何被解释。EXTRACT(type FROM date)函数从日期中返回“type”间隔。下表显示了typeexpr参数怎样被关联:
type 含义 期望的expr格式
SECOND SECONDS
MINUTE 分钟 MINUTES
HOUR 时间 HOURS
DAY DAYS
MONTH MONTHS
YEAR YEARS
MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS"
HOUR_MINUTE 小时和分钟 "HOURS:MINUTES"
DAY_HOUR 天和小时 "DAYS HOURS"
YEAR_MONTH 年和月 "YEARS-MONTHS"
HOUR_SECOND 小时, 分钟, "HOURS:MINUTES:SECONDS"
DAY_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES"
DAY_SECOND 天, 小时, 分钟, 秒 "DAYS HOURS:MINUTES:SECONDS"

MySQLexpr格式中允许任何标点分隔符。表示显示的是建议的分隔符。如果date参数是一个DATE值并且你的计算仅仅包含YEARMONTHDAY部分(即,没有时间部分),结果是一个DATE值。否则结果是一个DATETIME值。

mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
        -> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
        -> 1998-01-01
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
       -> 1997-12-31 23:59:59 
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
                       INTERVAL 1 SECOND);
        -> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
                       INTERVAL 1 DAY);
        -> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
                       INTERVAL "1:1" MINUTE_SECOND);
        -> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
                       INTERVAL "1 1:1:1" DAY_SECOND);
        -> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
                       INTERVAL "-1 10" DAY_HOUR);
        -> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
        -> 1997-12-02
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
       -> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
       -> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
       -> 20102

如果你指定太短的间隔值(不包括type关键词期望的间隔部分),MySQL假设你省掉了间隔值的最左面部分。例如,如果你指定一个typeDAY_SECOND,值expr被希望有天、小时、分钟和秒部分。如果你象"1:10"这样指定值,MySQL假设日子和小时部分是丢失的并且值代表分钟和秒。换句话说,"1:10" DAY_SECOND以它等价于"1:10" MINUTE_SECOND的方式解释,这对那MySQL解释TIME值表示经过的时间而非作为一天的时间的方式有二义性。如果你使用确实不正确的日期,结果是NULL。如果你增加MONTHYEAR_MONTHYEAR并且结果日期大于新月份的最大值天数,日子在新月用最大的天调整。

mysql> select DATE_ADD('1998-01-30', Interval 1 month);
        -> 1998-02-28
注意,从前面的例子中词INTERVALtype关键词不是区分大小写的。  
TO_DAYS(date)
给出一个日期date,返回一个天数(从0年的天数)。
mysql> select TO_DAYS(950501);
        -> 728779
mysql> select TO_DAYS('1997-10-07');
        -> 729669

TO_DAYS()不打算用于使用格列高里历(1582)出现前的值。

FROM_DAYS(N)
给出一个天数N,返回一个DATE值。
mysql> select FROM_DAYS(729669);
        -> '1997-10-07'

TO_DAYS()不打算用于使用格列高里历(1582)出现前的值。

DATE_FORMAT(date,format)
根据format字符串格式化date值。下列修饰符可以被用在format字符串中:
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AMPM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”

所有的其他字符不做解释被复制到结果中。

mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
        -> 'Saturday October 1997'
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
                          '%D %y %a %d %m %b %j');
        -> '4th 97 Sat 04 10 Oct 277'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
                          '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'

MySQL3.23中,在格式修饰符字符前需要%。在MySQL更早的版本中,%是可选的。

TIME_FORMAT(time,format)
这象上面的DATE_FORMAT()函数一样使用,但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。其他修饰符产生一个NULL值或0
CURDATE()
 
CURRENT_DATE
'YYYY-MM-DD'YYYYMMDD格式返回今天日期值,取决于函数是在一个字符串还是数字上下文被使用。
mysql> select CURDATE();
        -> '1997-12-15'
mysql> select CURDATE() + 0;
        -> 19971215
CURTIME()
 
CURRENT_TIME
'HH:MM:SS'HHMMSS格式返回当前时间值,取决于函数是在一个字符串还是在数字的上下文被使用。
mysql> select CURTIME();
        -> '23:50:26'
mysql> select CURTIME() + 0;
        -> 235026
NOW()
 
SYSDATE()
 
CURRENT_TIMESTAMP
'YYYY-MM-DD HH:MM:SS'YYYYMMDDHHMMSS格式返回当前的日期和时间,取决于函数是在一个字符串还是在数字的上下文被使用。
mysql> select NOW();
        -> '1997-12-15 23:50:26'
mysql> select NOW() + 0;
        -> 19971215235026
UNIX_TIMESTAMP()
 
UNIX_TIMESTAMP(date)
如果没有参数调用,返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一个date参数被调用,它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME字符串、一个TIMESTAMP或以YYMMDDYYYYMMDD格式的本地时间的一个数字。
mysql> select UNIX_TIMESTAMP();
        -> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
        -> 875996580

UNIX_TIMESTAMP被用于一个TIMESTAMP列,函数将直接接受值,没有隐含的“string-to-unix-timestamp”变换。

FROM_UNIXTIME(unix_timestamp)
'YYYY-MM-DD HH:MM:SS'YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值,取决于函数是在一个字符串还是或数字上下文中被使用。
mysql> select FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
mysql> select FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300
FROM_UNIXTIME(unix_timestamp,format)
返回表示 Unix 时间标记的一个字符串,根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符。
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
                            '%Y %D %M %h:%i:%s %x');
        -> '1997 23rd December 03:43:30 x'
SEC_TO_TIME(seconds)
返回seconds参数,变换成小时、分钟和秒,值以'HH:MM:SS'HHMMSS格式化,取决于函数是在一个字符串还是在数字上下文中被使用。
mysql> select SEC_TO_TIME(2378);
        -> '00:39:38'
mysql> select SEC_TO_TIME(2378) + 0;
        -> 3938
TIME_TO_SEC(time)
返回time参数,转换成秒。
mysql> select TIME_TO_SEC('22:23:00');
        -> 80580
mysql> select TIME_TO_SEC('00:39:38');
        -> 2378

7.4.12 其他函数

DATABASE()
返回当前的数据库名字。
mysql> select DATABASE();
        -> 'test'

如果没有当前的数据库,DATABASE()返回空字符串。

USER()
 
SYSTEM_USER()
 
SESSION_USER()
返回当前MySQL用户名。
mysql> select USER();
        -> 'davida@localhost'

在MySQL 3.22.11或以后版本中,这包括用户名和客户主机名。你可以象这样只提取用户名部分(值是否包括主机名部分均可工作):

mysql> select substring_index(USER(),"@",1);
        -> 'davida'
PASSWORD(str)
从纯文本口令str计算一个口令字符串。该函数被用于为了在user授权表的Password列中存储口令而加密MySQL口令。
mysql> select PASSWORD('badpwd');
        -> '7f84554057dd964b'

PASSWORD()加密是非可逆的。PASSWORD()不以与Unix口令加密的相同的方法执行口令加密。你不应该假定如果你的Unix 口令和你的MySQL口令是一样的,PASSWORD()将导致与在Unix口令文件存储的相同的加密值。见ENCRYPT()

ENCRYPT(str[,salt])
使用Unix crypt()系统调用加密strsalt参数应该是一个有2个字符的字符串。(MySQL 3.22.16中,salt可以长于2个字符。)
mysql> select ENCRYPT("hello");
        -> 'VxuFAJXVARROc'

如果crypt()在你的系统上不可用,ENCRYPT()总是返回NULLENCRYPT()只保留str起始8个字符而忽略所有其他,至少在某些系统上是这样。这将由底层的crypt()系统调用的行为决定。

ENCODE(str,pass_str)
使用pass_str作为口令加密str。为了解密结果,使用DECODE()。结果是一个二进制字符串,如果你想要在列中保存它,使用一个BLOB列类型。
DECODE(crypt_str,pass_str)
使用pass_str作为口令解密加密的字符串crypt_strcrypt_str应该是一个由ENCODE()返回的字符串。
MD5(string)
对字符串计算MD5校验和。值作为一个32长的十六进制数字被返回可以,例如用作哈希(hash)键。
mysql> select MD5("testing")
        -> 'ae2b1fca515949e5d54fb22b8ed95575'

这是一个“RSA数据安全公司的MD5消息摘要算法”。

LAST_INSERT_ID([expr])
返回被插入一个AUTO_INCREMENT列的最后一个自动产生的值。见20.4.29 mysql_insert_id()
mysql> select LAST_INSERT_ID();
        -> 195

产生的最后ID以每个连接为基础在服务器被维护,它不会被其他客户改变。如果你更新另外一个有非魔术值(即,不是NULL和不是0的一个值)的AUTO_INCREMENT列,它甚至不会被改变。如果expr作为一个参数在一个UPDATE子句的LAST_INSERT_ID()里面给出,那么参数值作为一个LAST_INSERT_ID()值被返回。这可以用来模仿顺序:首先创建表:

mysql> create table sequence (id int not null);
mysql> insert into sequence values (0);

然后表能被用来产生顺序号,象这样:

mysql> update sequence set id=LAST_INSERT_ID(id+1);

你可以不调用LAST_INSERT_ID()而产生顺序,但是这样使用函数的实用程序在服务器上自动维护ID值作为最后自动产生的值。你可以检索新的ID值,就像你能读入正常MySQL中的任何正常的AUTO_INCREMENT值一样。例如,LAST_INSERT_ID()(没有一个参数 )将返回新ID。C API函数mysql_insert_id()也可被用来得到值。

FORMAT(X,D)
格式化数字X为类似于格式'#,###,###.##',四舍五入到D为小数。如果D0,结果将没有小数点和小数部分。
mysql> select FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> select FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> select FORMAT(12332.2,0);
        -> '12,332'
VERSION()
返回表明MySQL服务器版本的一个字符串。
mysql> select VERSION();
        -> '3.22.19b-log'
GET_LOCK(str,timeout)
试图获得由字符串str给定的一个名字的锁定,第二个timeout为超时。如果锁定成功获得,返回1,如果尝试超时了,返回0,或如果发生一个错误,返回NULL(例如从存储器溢出或线程用mysqladmin kill被杀死)。当你执行RELEASE_LOCK()时、执行一个新的GET_LOCK()或线程终止时,一个锁定被释放。该函数可以用来实现应用锁或模拟记录锁,它阻止其他客户用同样名字的锁定请求;赞成一个给定的锁定字符串名字的客户可以使用字符串执行子协作建议的锁定。
mysql> select GET_LOCK("lock1",10);
        -> 1
mysql> select GET_LOCK("lock2",10);
        -> 1
mysql> select RELEASE_LOCK("lock2");
        -> 1
mysql> select RELEASE_LOCK("lock1");
        -> NULL

注意,第二个RELEASE_LOCK()调用返回NULL,因为锁"lock1"自动地被第二个GET_LOCK()调用释放。

RELEASE_LOCK(str)
释放字符串str命名的通过GET_LOCK()获得的锁。如果锁被释放,返回1,如果锁没被这个线程锁定(在此情况下锁没被释放)返回0,并且如果命名的锁不存在,返回NULL。如果锁从来没有通过调用GET_LOCK()获得或如果它已经被释放了,锁将不存在。
BENCHMARK(count,expr)
BENCHMARK()函数重复countTimes次执行表达式expr,它可以用于计时MySQL处理表达式有多快。结果值总是0。意欲用于mysql客户,它报告查询的执行时间。
mysql> select BENCHMARK(1000000,encode("hello","goodbye"));
+----------------------------------------------+
| BENCHMARK(1000000,encode("hello","goodbye")) |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (4.74 sec)

报告的时间是客户端的经过时间,不是在服务器端的CPU时间。执行BENCHMARK()若干次可能是明智的,并且注意服务器机器的负载有多重来解释结果。

7.4.13 与GROUP BY子句一起使用的函数

如果你在不包含GROUP BY子句的一个语句中使用聚合函数,它等价于聚合所有行。

COUNT(expr)
返回由一个SELECT语句检索出来的行的非NULL值的数目。
mysql> select student.student_name,COUNT(*)
           from student,course
           where student.student_id=course.student_id
           GROUP BY student_name;

COUNT(*)在它返回的检索出来的行数目上有些不同,不管他们是否包含NULL值。如果SELECT从一个表检索,或没有检索出其他列并且没有WHERE子句,COUNT(*)被优化以便快速地返回。例如:

mysql> select COUNT(*) from student;
  
COUNT(DISTINCT expr,[expr...])
返回一个不同值的数目。
mysql> select COUNT(DISTINCT results) from student;

MySQL中,你可以通过给出一个表达式列表以得到不同的表达式组合的数目。在 ANSI SQL中,你可能必须在CODE(DISTINCT ..)内进行所有表达式的连接。

AVG(expr)
返回expr的平均值。
mysql> select student_name, AVG(test_score)
           from student
           GROUP BY student_name;
  
MIN(expr)
 
MAX(expr)
返回expr的最小或最大值。MIN()MAX()可以有一个字符串参数;在这种的情况下,他们返回最小或最大的字符串值。
mysql> select student_name, MIN(test_score), MAX(test_score)
           from student
           GROUP BY student_name;
 
SUM(expr)
返回expr的和。注意,如果返回的集合没有行,它返回NULL!
STD(expr)
 
STDDEV(expr)
返回expr标准差(deviation)。这是对 ANSI SQL 的扩展。该函数的形式STDDEV()是提供与Oracle的兼容性。
BIT_OR(expr)
返回expr里所有位的位或。计算用 64 位(BIGINT)精度进行。
BIT_AND(expr)
返回expr里所有位的位与。计算用 64 位(BIGINT)精度进行。

MySQL扩展了GROUP BY的用法。你可以不出现在的GROUP BY部分的SELECT表达式中使用列或计算,这表示这个组的任何可能值。你可以使用它是性能更好,避免在不必要的项目上排序和分组。例如,你在下列查询中不需要在customer.name上聚合:

mysql> select order.custid,customer.name,max(payments)
       from order,customer
       where order.custid = customer.custid
       GROUP BY order.custid;

在 ANSI SQL中,你将必须将customer.name加到GROUP BY子句。在MySQL中,名字是冗余的。

如果你从GROUP BY部分省略的列在组中不是唯一的,不要使用这个功能。

在某些情况下,你可以使用MIN()MAX()获得一个特定的列值,即使它不是唯一的。下例给出从包含sort列中最小值的行的column值:

substr(MIN(concat(sort,space(6-length(sort)),column),7,length(column)))

注意,如果你正在使用MySQL 3.22(或更早)或如果你正在试图遵从ANSI SQL,你不能在GROUP BYORDER BY子句中使用表达式。你可以通过使用表达式的一个别名解决此限制:

mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
           GROUP BY id,val ORDER BY val;

MySQL3.23中,你可以这样做:

mysql> SELECT id,FLOOR(value/100) FROM tbl_name ORDER BY RAND();

7.5 CREATE DATABASE句法

CREATE DATABASE db_name

CREATE DATABASE用给定的名字创建一个数据库。允许的数据库名字规则在7.1.5 数据库、表、索引、列和别名命名中给出。如果数据库已经存在,发生一个错误。

MySQL中的数据库实现成包含对应数据库中表的文件的目录。因为数据库在初始创建时没有任何表,CREATE DATABASE语句只是在MySQL数据目录下面创建一个目录。

你也可以用mysqladmin创建数据库。见12.1 不同的MySQL程序的概述

7.6 DROP DATABASE句法

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE删除数据库中的所有表和数据库。要小心地使用这个命令!

DROP DATABASE返回从数据库目录被删除的文件的数目。通常,这3倍于表的数量,因为每张表对应于一个“.MYD”文件、一个“.MYI”文件和一个“.frm”文件。

MySQL 3.22或以后版本中,你可以使用关键词IF EXISTS阻止一个错误的发生,如果数据库不存在。

你也可以用mysqladmin丢弃数据库。见12.1 不同的 MySQL 程序的概述

7.7 CREATE TABLE句法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  or    CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  or    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  or    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  or    INT[(length)] [UNSIGNED] [ZEROFILL]
  or    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  or    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  or    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  or    NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  or    CHAR(length) [BINARY]
  or    VARCHAR(length) [BINARY]
  or    DATE
  or    TIME
  or    TIMESTAMP
  or    DATETIME
  or    TINYBLOB
  or    BLOB
  or    MEDIUMBLOB
  or    LONGBLOB
  or    TINYTEXT
  or    TEXT
  or    MEDIUMTEXT
  or    LONGTEXT
  or    ENUM(value1,value2,value3,...)
  or    SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
	TYPE = {ISAM | MYISAM | HEAP}
or	AUTO_INCREMENT = #
or	AVG_ROW_LENGTH = #
or	CHECKSUM = {0 | 1}
or	COMMENT = "string"
or	MAX_ROWS = #
or	MIN_ROWS = #
or	PACK_KEYS = {0 | 1}
or	PASSWORD = "string"
or	DELAY_KEY_WRITE = {0 | 1}
or      ROW_FORMAT= { default | dynamic | static | compressed }

select_statement:
	[IGNORE | REPLACE] SELECT ...  (Some legal select statement)

CREATE TABLE在当前数据库中用给出的名字创建一个数据库表。允许的表名的规则在7.1.5 数据库,表,索引,列和别名命名中给出。如果当前数据库不存在或如果表已经存在,出现一个错误。

MySQL3.22或以后版本中,表名可以被指定为db_name.tbl_name,不管有没有当前的数据库都可以。

MySQL3.23中,当你创建一张表时,你可以使用TEMPORARY关键词。如果一个连接死掉,临时表将自动被删除,并且其名字是按连接命名。这意味着,2个不同的连接能使用相同的暂时表的名字而不会彼此冲突或与相同名字的现有数据库表冲突。(现有的表被隐蔽直到临时表被删除)。

MySQL3.23或以后版本中,你可以使用关键词IF NOT EXISTS以便如果表已经存在不发生一个错误。注意,无法证实表结构是相同的。

每张表tbl_name由在数据库目录的一些文件表示。在MyISAM类型的表的情况下,你将得到:

文件 目的
tbl_name.frm 表定义(表格)文件
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件

对于各种列类型的性质的更多信息,见7.3 列类型

7.7.1 隐含的列说明改变

在某些情况下,MySQL隐含地改变在一个CREATE TABLE语句给出的一个列说明。(这也可能在ALTER TABLE。)

如果你想要知道MySQL是否使用了除你指定的以外的一种列类型,在创建或改变你的表之后,发出一个DESCRIBE tbl_name语句即可。

如果你使用myisampack压缩一个表,可能会发生改变某些其他的列类型。见10.6.3 压缩表的特征。 

7.8 ALTER TABLE句法

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
  or    MODIFY [COLUMN] create_definition
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    RENAME [AS] new_tbl_name
  or    table_options

ALTER TABLE允许你修改一个现有表的结构。例如,你可以增加或删除列、创造或消去索引、改变现有列的类型、或重新命名列或表本身。你也能改变表的注释和表的类型。见7.7 CREATE TABLE句法

如果你使用ALTER TABLE修改一个列说明但是DESCRIBE tbl_name显示你的列并没有被修改,这可能是MySQL因为在7.7.1 隐含的列说明改变中描述的原因之一而忽略了你的修改。例如,如果你试图将一个VARCHAR改为CHARMySQL将仍然使用VARCHAR,如果表包含其他变长的列。

ALTER TABLE通过制作原来表的一个临时副本来工作。修改在副本上施行,然后原来的表被删除并且重新命名一个新的。这样做使得所有的修改自动地转向到新表,没有任何失败的修改。当ALTER TABLE正在执行时,原来的桌可被其他客户读取。更新和写入表被延迟到新表准备好了为止。

这里是一个例子,显示了一些ALTER TABLE用法。我们以一个如下创建的表t1开始:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

重命名表,从t1t2:

mysql> ALTER TABLE t1 RENAME t2;

为了改变列a,从INTEGER改为TINYINT NOT NULL(名字一样),并且改变列b,从CHAR(10)改为CHAR(20),同时重命名它,从b改为c

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

增加一个新TIMESTAMP列,名为d

mysql> ALTER TABLE t2 ADD d TIMESTAMP; 

在列d上增加一个索引,并且使列a为主键:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

删出列c

mysql> ALTER TABLE t2 DROP COLUMN c;

增加一个新的AUTO_INCREMENT整数列,命名为c

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

注意,我们索引了c,因为AUTO_INCREMENT柱必须被索引,并且另外我们声明cNOT NULL,因为索引了的列不能是NULL

当你增加一个AUTO_INCREMENT列时,自动地用顺序数字填入列值。

7.9 OPTIMIZE TABLE句法

OPTIMIZE TABLE tbl_name

如果你删除了一个表的大部分或如果你用变长的行对一个表(有VARCHARBLOBTEXT列的表)做了改变,应该使用OPTIMZE TABLE。删除的记录以一个链接表维持并且随后的INSERT操作再次使用老记录的位置。你可以使用OPTIMIZE TABLE回收闲置的空间。

OPTIMIZE TABLE通过制作原来的表的一个临时副本来工作。老的表子被拷贝到新表中(没有闲置的行),然后原来的表被删除并且重命名一个新的。这样做使得所有更新自动转向新的表,没有任何失败的更新。当时OPTIMIZE TABLE正在执行时,原来的表可被另外的客户读取。对表的更新和写入延迟到新表是准备好为止。

7.10 DROP TABLE句法

DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]

DROP TABLE删除一个或多个数据库表。所有表中的数据和表定义均被删除,故小心使用这个命令!

MySQL 3.22或以后版本,你可以使用关键词IF EXISTS类避免不存在表的一个错误发生。

7.11 DELETE句法

DELETE [LOW_PRIORITY] FROM tbl_name
    [WHERE where_definition] [LIMIT rows]

DELETEtbl_name表中删除满足由where_definition给出的条件的行,并且返回删除记录的个数。

如果你发出一个没有WHERE子句的DELETE,所有行都被删除。MySQL通过创建一个空表来完成,它比删除每行要快。在这种情况下,DELETE返回零作为受影响记录的数目。(MySQL不能返回实际上被删除的行数,因为进行再创建而不是打开数据文件。只要表定义文件“tbl_name.frm”是有效的,表才能这样被再创建,即使数据或索引文件破坏了)。

如果你确实想要知道在你正在删除所有行时究竟有对少记录被删除,并且愿意承受速度上的惩罚,你可以这种形式的一个ELETE语句:

mysql> DELETE FROM tbl_name WHERE 1>0;

注意这比没有WHERE子句的DELETE FROM tbl_name慢的多了,因为它一次删除一行。

如果你指定关键词LOW_PRIORITYDELETE的执行被推迟到没有其他客户读取表后。

删除的记录以一个链接表维持并且随后的INSERT操作再次使用老的记录位置。为了回收闲置的空间并减小文件大小,使用OPTIMIZE TABLE语句或myisamchk实用程序重新组织表。OPTIMIZE TABLE较容易,但是myisamchk更快。见7.9 OPTIMIZE TABLE句法13.4.3 表优化

MySQLDELETE特定的LIMIT rows选项告诉服务器在控制被返回到客户之前,将要删除的最大行数,这可以用来保证一个特定DELETE命令不会花太多的时间。你可以简单地重复DELETE命令直到受影响的行数小于LIMIT值。

7.12 SELECT句法

SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
        [WHERE where_definition]
        [GROUP BY col_name,...]
        [HAVING where_definition]
        [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
        [LIMIT [offset,] rows]
        [PROCEDURE procedure_name] ]

SELECT被用来检索从一个或多个表中精选的行。select_expression指出你想要检索的列。SELECT也可以用来检索不引用任何表的计算行。例如:

mysql> SELECT 1 + 1;
         -> 2

所有使用的关键词必须精确地以上面的顺序给出。例如,一个HAVING子句必须跟在GROUP BY子句之后和ORDER BY子句之前。

如果你使用INTO DUMPFILE而不是INTO OUTFILEMySQL将只写一行到文件中,没有任何列或行结束并且没有任何转义。如果你想要在一个文件存储一个blob,这是很有用的。

7.13 JOIN句法

MySQL支持下列用于SELECT语句的JOIN句法:

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }

上述最后的LEFT OUTER JOIN的句法只是为了与ODBC兼容而存在的。

一些例子:

mysql> select * from table1,table2 where table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 USING (id);
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id
           LEFT JOIN table3 ON table2.id=table3.id;

10.5.4 MySQL怎样优化LEFT JOIN

7.14 INSERT句法

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
或  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
或  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression, ...

INSERT把新行插入到一个存在的表中,INSERT ... VALUES形式的语句基于明确指定的值插入行,INSERT ... SELECT形式插入从其他表选择的行,有多个值表的INSERT ... VALUES的形式在MySQL 3.22.5或以后版本中支持,col_name=expression语法在MySQL 3.22.10或以后版本中支持。

tbl_name是行应该被插入其中的表。列名表或SET子句指出语句为那一列指定值。

如果你使用INSERT ... SELECTINSERT ... VALUES语句有多个值列表,你可以使用C API函数mysql_info()得到查询的信息。信息字符串的格式如下:

Records: 100 Duplicates: 0 Warnings: 0 

Duplicates指出不能被插入的行的数量,因为他们与现有的唯一的索引值重复。Warnings指出在出现某些问题时尝试插入列值的次数。在下列任何条件下都可能发生错误:

对于INSERT语句的DELAYED选项是MySQL专属的选项-如果你客户有不能等到INSERT完成,它是很有用的。当你为日记登录使用MySQL时,而且你也周期性地运行花很长时间完成的SELECT语句,这是一个常见的问题。DELAYED在面MySQL 3.22.15中被引入,它是MySQL对 ANSI SQL92 的一个扩展。

当你使用INSERT DELAYED时,客户将马上准备好,并且当表不被任何其他的线程使用时,行将被插入。

另一个使用INSERT DELAYED的主要好处是从很多客户插入被捆绑在一起并且写进一个块。这比做很多单独的插入要来的快。

注意,当前排队的行只是存储在内存中,直到他们被插入到表中。这意味着,如果你硬要杀死mysqld(kill -9)或如果mysqld出人意料地死掉,没被写进磁盘的任何排队的行被丢失!

下列详细描述当你为INSERTREPLACE使用DELAYED选项时,发生什么。在这个描述中,“线程”是收到一个INSERT DELAYED命令的线程并且“处理器”是处理所有对于一个特定表的INSERT DELAYED语句。

注意如果表不在使用,INSERT DELAYED比一个正常的INSERT慢。对服务器也有额外开销来处理你对它使用INSERT DELAYED的每个表的一个单独线程。这意味着,你应该只在你确实肯定需要它的时候才使用INSERT DELAYED

7.15 REPLACE句法

    REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...)
或  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
或  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression,...
 

REPLACE功能与INSERT完全一样,除了如果在表中的一个老记录具有在一个唯一索引上的新记录有相同的值,在新记录被插入之前,老记录被删除。见7.14 INSERT句法

7.16 LOAD DATA INFILE句法

LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。如果指定LOCAL关键词,从客户主机读文件。如果LOCAL没指定,文件必须位于服务器上。(LOCALMySQL3.22.6或以后版本中可用。)

为了安全原因,当读取位于服务器上的文本文件时,文件必须处于数据库目录或可被所有人读取。另外,为了对服务器上文件使用LOAD DATA INFILE,在服务器主机上你必须有file的权限。见6.5 由MySQL提供的权限

如果你指定关键词LOW_PRIORITYLOAD DATA语句的执行被推迟到没有其他客户读取表后。

使用LOCAL将比让服务器直接存取文件慢些,因为文件的内容必须从客户主机传送到服务器主机。在另一方面,你不需要file权限装载本地文件。

你也可以使用mysqlimport实用程序装载数据文件;它由发送一个LOAD DATA INFILE命令到服务器来运作。 --local选项使得mysqlimport从客户主机上读取数据。如果客户和服务器支持压缩协议,你能指定--compress在较慢的网络上获得更好的性能。

当在服务器主机上寻找文件时,服务器使用下列规则:

注意这些规则意味着一个像“./myfile.txt”给出的文件是从服务器的数据目录读取,而作为“myfile.txt”给出的一个文件是从当前数据库的数据库目录下读取。也要注意,对于下列哪些语句,对db1文件从数据库目录读取,而不是db2

mysql> USE db1;
mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table; 

REPLACEIGNORE关键词控制对现有的唯一键记录的重复的处理。如果你指定REPLACE,新行将代替有相同的唯一键值的现有行。如果你指定IGNORE,跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项,当找到重复键键时,出现一个错误,并且文本文件的余下部分被忽略时。

如果你使用LOCAL关键词从一个本地文件装载数据,服务器没有办法在操作的当中停止文件的传输,因此缺省的行为好像IGNORE被指定一样。

LOAD DATA INFILESELECT ... INTO OUTFILE的逆操作,见7.12 SELECT句法。为了将一个数据库的数据写入一个文件,使用SELECT ... INTO OUTFILE,为了将文件读回数据库,使用LOAD DATA INFILE。两个命令的FIELDSLINES子句的语法是相同的。两个子句是可选的,但是如果指定两个,FIELDS必须在LINES之前。

如果你指定一个FIELDS子句,它的每一个子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BYESCAPED BY)也是可选的,除了你必须至少指定他们之一。

如果你不指定一个FIELDS子句,缺省值与如果你这样写的相同:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

如果你不指定一个LINES子句,缺省值与如果你这样写的相同:

LINES TERMINATED BY '\n' 

换句话说,缺省值导致读取输入时,LOAD DATA INFILE表现如下:

相反,缺省值导致在写入输出时,SELECT ... INTO OUTFILE表现如下:

注意,为了写入FIELDS ESCAPED BY '\\',对作为一条单个的反斜线被读取的值,你必须指定2条反斜线值。

IGNORE number LINES选项可被用来忽略在文件开始的一个列名字的头:

mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;

当你与LOAD DATA INFILE一起使用SELECT ... INTO OUTFILE将一个数据库的数据写进一个文件并且随后马上将文件读回数据库时,两个命令的字段和处理选项必须匹配,否则,LOAD DATA INFILE将不能正确解释文件的内容。假定你使用SELECT ... INTO OUTFILE将由逗号分隔的字段写入一个文件:

mysql> SELECT * FROM table1 INTO OUTFILE 'data.txt'
           FIELDS TERMINATED BY ','
           FROM ...

为了将由逗号分隔的文件读回来,正确的语句将是:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
           FIELDS TERMINATED BY ',';

相反,如果你试图用下面显示的语句读取文件,它不会工作,因为它命令LOAD DATA INFILE在字段之间寻找定位符:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
           FIELDS TERMINATED BY '\t';

可能的结果是每个输入行将被解释为单个的字段。

LOAD DATA INFILE能被用来读取从外部来源获得的文件。例如,以dBASE格式的文件将有由逗号分隔并用双引号包围的字段。如果文件中的行由换行符终止,下面显示的命令说明你将用来装载文件的字段和行处理选项:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
           LINES TERMINATED BY '\n';

任何字段或行处理选项可以指定一个空字符串('')。如果不是空,FIELDS [OPTIONALLY] ENCLOSED BYFIELDS ESCAPED BY值必须是一个单个字符。FIELDS TERMINATED BYLINES TERMINATED BY值可以是超过一个字符。例如,写入由回车换行符对(CR+LF)终止的行,或读取包含这样行的一个文件,指定一个LINES TERMINATED BY '\r\n'子句。

FIELDS [OPTIONALLY] ENCLOSED BY控制字段的包围字符。对于输出(SELECT ... INTO OUTFILE),如果你省略OPTIONALLY,所有的字段由ENCLOSED BY字符包围。对于这样的输出的一个例子(使用一个逗号作为字段分隔符)显示在下面:

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

如果你指定OPTIONALLYENCLOSED BY字符仅被用于包围CHARVARCHAR字段:

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

注意,一个字段值中的ENCLOSED BY字符的出现通过用ESCAPED BY字符作为其前缀来转义。也要注意,如果你指定一个空ESCAPED BY值,可能产生不能被LOAD DATA INFILE正确读出的输出。例如,如果转义字符为空,上面显示的输出显示如下。注意到在第四行的第二个字段包含跟随引号的一个逗号,它(错误地)好象要终止字段:

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

对于输入,ENCLOSED BY字符如果存在,它从字段值的尾部被剥去。(不管是否指定OPTIONALLY都是这样;OPTIONALLY对于输入解释不起作用)由ENCLOSED BY字符领先的ESCAPED BY字符出现被解释为当前字段值的一部分。另外,出现在字段中重复的ENCLOSED BY被解释为单个ENCLOSED BY字符,如果字段本身以该字符开始。例如,如果ENCLOSED BY '"'被指定,引号如下处理:

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY控制如何写入或读出特殊字符。如果FIELDS ESCAPED BY字符不是空的,它被用于前缀在输出上的下列字符:

如果FIELDS ESCAPED BY字符是空的,没有字符被转义。指定一个空转义字符可能不是一个好主意,特别是如果在你数据中的字段值包含刚才给出的表中的任何字符。

对于输入,如果FIELDS ESCAPED BY字符不是空的,该字符的出现被剥去并且后续字符在字面上作为字段值的一个部分。例外是一个转义的“0”“N”(即,\0\N,如果转义字符是“\”)。这些序列被解释为ASCII 0(一个零值字节)和NULL。见下面关于NULL处理的规则。

对于更多关于“\”- 转义句法的信息,见7.1 文字:怎样写字符串和数字

在某些情况下,字段和行处理选项相互作用:

NULL值的处理有多种,取决于你使用的FIELDSLINES选项:

一些不被LOAD DATA INFILE支持的情况:

下列例子装载所有persondata表的行:

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

没有指定字段表,所以LOAD DATA INFILE期望输入行对每个表列包含一个字段。使用缺省FIELDSLINES值。

如果你希望仅仅装载一张表的某些列,指定一个字段表:

mysql> LOAD DATA INFILE 'persondata.txt'
           INTO TABLE persondata (col1,col2,...);

如果在输入文件中的字段顺序不同于表中列的顺序,你也必须指定一个字段表。否则,MySQL不能知道如何匹配输入字段和表中的列。

如果一个行有很少的字段,对于不存在输入字段的列被设置为缺省值。缺省值赋值在7.7 CREATE TABLE句法中描述。

如果字段值缺省,空字段值有不同的解释:

如果列有一个NULL,或(只对第一个TIMESTAMP列)在指定一个字段表时,如果TIMESTAMP列从字段表省掉,TIMESTAMP列只被设置为当前的日期和时间。

如果输入行有太多的字段,多余的字段被忽略并且警告数字加1。

LOAD DATA INFILE认为所有的输入是字符串,因此你不能像你能用INSERT语句的ENUMSET列的方式使用数字值。所有的ENUMSET值必须作为字符串被指定!

如果你正在使用C API,当LOAD DATA INFILE查询完成时,你可通过调用API函数mysql_info()得到有关查询的信息。信息字符串的格式显示在下面:

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

当值通过INSERT语句插入时,在某些情况下出现警告(见7.14 INSERT句法),除了在输入行中有太少或太多的字段时,LOAD DATA INFILE也产生警告。警告没被存储在任何地方;警告数字仅能用于表明一切是否顺利。如果你得到警告并且想要确切知道你为什么得到他们,一个方法是使用SELECT ... INTO OUTFILE到另外一个文件并且把它与你的原版输入文件比较。

对于有关INSERT相对LOAD DATA INFILE的效率和加快LOAD DATA INFILE的更多信息,见10.5.6 加速INSERT查询

 

7.17 UPDATE句法

UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,...
    [WHERE where_definition] [LIMIT #]

UPDATE用新值更新现存表中行的列,SET子句指出哪个列要修改和他们应该被给定的值,WHERE子句,如果给出,指定哪个行应该被更新,否则所有行被更新。

如果你指定关键词LOW_PRIORITY,执行UPDATE被推迟到没有其他客户正在读取表时。

如果你从一个表达式的tbl_name存取列,UPDATE使用列的当前值。例如,下列语句设置age为它的当前值加1:

mysql> UPDATE persondata SET age=age+1;

UPDATE赋值是从左到右计算。例如,下列语句两倍age列,然后加1:

mysql> UPDATE persondata SET age=age*2, age=age+1;

如果你设置列为其它当前有的值,MySQL注意到这点并且不更新它。

UPDATE返回实际上被改变的行的数量。在MySQL 3.22或以后版本中,C API函数mysql_info()返回被匹配并且更新的行数和在UPDATE期间发生警告的数量。

MySQL3.23中,你可使用LIMIT #来保证只有一个给定数量的行被改变。

 

7.18 USE句法

USE db_name 

USE db_name语句告诉MySQL使用db_name数据库作为随后的查询的缺省数据库。数据库保持到会话结束,或发出另外一个USE语句:

mysql> USE db1;
mysql> SELECT count(*) FROM mytable;      # selects from db1.mytable
mysql> USE db2;
mysql> SELECT count(*) FROM mytable;      # selects from db2.mytable

利用USE语句使得一个特定的数据库称为当前数据库并不阻止你访问在另外的数据库中的表。下面的例子访问db1数据库中的author表和db2数据库中的editor表:

mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
           WHERE author.editor_id = db2.editor.editor_id;

USE语句提供了Sybase的兼容性。

 

7.19 FLUSH句法(清除缓存)

FLUSH flush_option [,flush_option]

如果你想要清除一些MySQL使用内部缓存,你应该使用FLUSH命令。为了执行FLUSH,你必须有reload权限。

flush_option可以是下列任何东西:

HOSTS 清空主机缓存表。如果你的某些主机改变IP数字,或如果你得到错误消息Host ... is blocked,你应该清空主机表。当在连接MySQL服务器时,对一台给定的主机有多于max_connect_errors个错误连续不断地发生,MySQL认定某些东西错了并且阻止主机进一步的连接请求。清空主机表允许主机再尝试连接。见18.2.3 Host '...' is blocked错误)。你可用-O max_connection_errors=999999999启动mysqld来避免这条错误消息。
LOGS 关闭并且再打开标准和更新记录文件。如果你指定了一个没有扩展名的更新记录文件,新的更新记录文件的扩展数字将相对先前的文件加1。
PRIVILEGES mysql数据库授权表中重新装载权限。
TABLES 关闭所有打开的表。
STATUS 重置大多数状态变量到0。

你也可以用mysqladmin实用程序,使用flush-hosts, flush-logs, reloadflush-tables命令来访问上述的每一个命令。

 

7.20 KILL句法

KILL thread_id 

每个对mysqld的连接以一个单独的线程运行。你可以用看SHOW PROCESSLIST命令察看哪个线程正在运行,并且用KILL thread_id命令杀死一个线程。

如果你有process权限,你能看到并且杀死所有线程。否则,你只能看到并且杀死你自己的线程。

你也可以使用mysqladmin processlistmysqladmin kill命令检查并杀死线程。

7.21 SHOW句法 (得到表,列等的信息)

   SHOW DATABASES [LIKE wild]
or SHOW TABLES [FROM db_name] [LIKE wild]
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW STATUS
or SHOW VARIABLES [LIKE wild]
or SHOW [FULL] PROCESSLIST
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW GRANTS FOR user

SHOW提供关于数据库、表、列或服务器的信息。如果使用LIKE wild部分,wild字符串可以是一个使用SQL的“%”“_”通配符的字符串。

你能使用db_name.tbl_name作为tbl_name FROM db_name句法的另一种选择。这两个语句是相等的:

mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;

SHOW DATABASES列出在MySQL服务器主机上的数据库。你也可以用mysqlshow命令得到这张表。

SHOW TABLES列出在一个给定的数据库中的表。你也可以用mysqlshow db_name命令得到这张表。

注意:如果一个用户没有一个表的任何权限,表将不在SHOW TABLESmysqlshow db_name中的输出中显示。

SHOW COLUMNS列出在一个给定表中的列。如果列类型不同于你期望的是基于CREATE TABLE语句的那样,注意,MySQL有时改变列类型。见7.7.1 隐含的列说明变化

DESCRIBE语句提供了类似SHOW COLUMNS的信息。见7.23 DESCRIBE 句法 (得到列的信息)

SHOW TABLE STATUS(在版本3.23引入)运行类似SHOW STATUS,但是提供每个表的更多信息。你也可以使用mysqlshow --status db_name命令得到这张表。下面的列被返回:

含义
Name 表名
Type 表的类型 (ISAM,MyISAM或HEAP)
Row_format 行存储格式 (固定, 动态, 或压缩)
Rows 行数量
Avg_row_length 平均行长度
Data_length 数据文件的长度
Max_data_length 数据文件的最大长度
Index_length 索引文件的长度
Data_free 已分配但未使用了字节数
Auto_increment 下一个 autoincrement(自动加1)值
Create_time 表被创造的时间
Update_time 数据文件最后更新的时间
Check_time 最后对表运行一个检查的时间
Create_options CREATE TABLE一起使用的额外选项
Comment 当创造表时,使用的注释 (或为什么MySQL不能存取表信息的一些信息)。

SHOW FIELDSSHOW COLUMNS一个同义词,SHOW KEYSSHOW INDEX一个同义词。你也可以用mysqlshow db_name tbl_namemysqlshow -k db_name tbl_name 列出一张表的列或索引。

SHOW INDEX以非常相似于ODBC的SQLStatistics调用的格式返回索引信息。下面的列被返回:

含义
Table 表名
Non_unique 0,如果索引不能包含重复。
Key_name 索引名
Seq_in_index 索引中的列顺序号, 从 1 开始。
Column_name 列名。
Collation 列怎样在索引中被排序。在MySQL中,这可以有值A(升序) 或NULL(不排序)。
Cardinality 索引中唯一值的数量。这可通过运行isamchk -a更改.
Sub_part 如果列只是部分被索引,索引字符的数量。NULL,如果整个键被索引。

SHOW STATUS提供服务器的状态信息(象mysqladmin extended-status一样)。输出类似于下面的显示,尽管格式和数字可以有点不同:

+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_clients          | 0      |
| Aborted_connects         | 0      |
| Connections              | 17     |
| Created_tmp_tables       | 0      |
| Delayed_insert_threads   | 0      |
| Delayed_writes           | 0      |
| Delayed_errors           | 0      |
| Flush_commands           | 2      |
| Handler_delete           | 2      |
| Handler_read_first       | 0      |
| Handler_read_key         | 1      |
| Handler_read_next        | 0      |
| Handler_read_rnd         | 35     |
| Handler_update           | 0      |
| Handler_write            | 2      |
| Key_blocks_used          | 0      |
| Key_read_requests        | 0      |
| Key_reads                | 0      |
| Key_write_requests       | 0      |
| Key_writes               | 0      |
| Max_used_connections     | 1      |
| Not_flushed_key_blocks   | 0      |
| Not_flushed_delayed_rows | 0      |
| Open_tables              | 1      |
| Open_files               | 2      |
| Open_streams             | 0      |
| Opened_tables            | 11     |
| Questions                | 14     |
| Slow_queries             | 0      |
| Threads_connected        | 1      |
| Threads_running          | 1      |
| Uptime                   | 149111 |
+--------------------------+--------+

上面列出的状态变量有下列含义:

Aborted_clients 由于客户没有正确关闭连接已经死掉,已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时,已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes INSERT DELAYED写入的行数。
Delayed_errors INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载)
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。

关于上面的一些注释:

SHOW VARIABLES显示出一些MySQL系统变量的值,你也能使用mysqladmin variables命令得到这个信息。如果缺省值不合适,你能在mysqld启动时使用命令行选项来设置这些变量的大多数。输出类似于下面的显示,尽管格式和数字可以有点不同:

+------------------------+--------------------------+
| Variable_name          | Value                    |
+------------------------+--------------------------+
| back_log               | 5                        |
| connect_timeout        | 5                        |
| basedir                | /my/monty/               |
| datadir                | /my/monty/data/          |
| delayed_insert_limit   | 100                      |
| delayed_insert_timeout | 300                      |
| delayed_queue_size     | 1000                     |
| join_buffer_size       | 131072                   |
| flush_time             | 0                        |
| interactive_timeout    | 28800                    |
| key_buffer_size        | 1048540                  |
| language               | /my/monty/share/english/ |
| log                    | OFF                      |
| log_update             | OFF                      |
| long_query_time        | 10                       |
| low_priority_updates   | OFF                      |
| max_allowed_packet     | 1048576                  |
| max_connections        | 100                      |
| max_connect_errors     | 10                       |
| max_delayed_threads    | 20                       |
| max_heap_table_size    | 16777216                 |
| max_join_size          | 4294967295               |
| max_sort_length        | 1024                     |
| max_tmp_tables         | 32                       |
| net_buffer_length      | 16384                    |
| port                   | 3306                     |
| protocol-version       | 10                       |
| record_buffer          | 131072                   |
| skip_locking           | ON                       |
| socket                 | /tmp/mysql.sock          |
| sort_buffer            | 2097116                  |
| table_cache            | 64                       |
| thread_stack           | 131072                   |
| tmp_table_size         | 1048576                  |
| tmpdir                 | /machine/tmp/            |
| version                | 3.23.0-alpha-debug       |
| wait_timeout           | 28800                    |
+------------------------+--------------------------+

10.2.3 调节服务器参数

SHOW PROCESSLIST显示哪个线程正在运行,你也能使用mysqladmin processlist命令得到这个信息。如果你有process权限, 你能看见所有的线程,否则,你仅能看见你自己的线程。见7.20 KILL句法。如果你不使用FULL选项,那么每个查询只有头100字符被显示出来。

SHOW GRANTS FOR user列出对一个用户必须发出以重复授权的授权命令。

mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root''localhost' WITH GRANT OPTION  |
+---------------------------------------------------------------------+
  

7.22 EXPLAIN句法(得到关于SELECT的信息)

    EXPLAIN tbl_name
or  EXPLAIN SELECT select_options

EXPLAIN tbl_nameDESCRIBE tbl_nameSHOW COLUMNS FROM tbl_name的一个同义词。

当你在一条SELECT语句前放上关键词EXPLAINMySQL解释它将如何处理SELECT,提供有关表如何联结和以什么次序联结的信息。

借助于EXPLAIN,你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT。你也能知道优化器是否以一个最佳次序联结表。为了强制优化器对一个SELECT语句使用一个特定联结次序,增加一个STRAIGHT_JOIN子句。

对于非简单的联结,EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以他们将被读入的顺序被列出。MySQL用一边扫描多次联结的方式解决所有联结,这意味着MySQL从第一个表中读一行,然后找到在第二个表中的一个匹配行,然后在第3个表中等等。当所有的表被处理完,它输出选择的列并且回溯表列表直到找到一个表有更多的匹配行,从该表读入下一行并继续处理下一个表。

EXPLAIN的输出包括下面列:

table
输出的行所引用的表。
type
联结类型。各种类型的信息在下面给出。
possible_keys
possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于表的次序。这意味着在possible_keys中的某些键实际上不能以生成的表次序使用。如果该列是空的,没有相关的索引。在这种情况下,你也许能通过检验WHERE子句看是否它引用某些列或列不是适合索引来提高你的查询性能。如果是这样,创造一个适当的索引并且在用EXPLAIN检查查询。见7.8 ALTER TABLE句法。为了看清一张表有什么索引,使用SHOW INDEX FROM tbl_name
key
key列显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL
key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL,长度是NULL。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。
ref
ref列显示哪个列或常数与key一起用于从表中选择行。
rows
rows列显示MySQL相信它必须检验以执行查询的行数。
Extra
如果Extra列包括文字Only index,这意味着信息只用索引树中的信息检索出的。通常,这比扫描整个表要快。如果Extra列包括文字where used,它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。

不同的联结类型列在下面,以最好到最差类型的次序:

system
表仅有一行(=系统表)。这是const联结类型的一个特例。
const
表有最多一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被剩下的优化器认为是常数。 const表很快,因为它们只读取一次!
eq_ref
对于每个来自于先前的表的行组合,从该表中读取一行。这可能是最好的联结类型,除了const类型。它用在一个索引的所有部分被联结使用并且索引是UNIQUEPRIMARY KEY
ref
对于每个来自于先前的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联结只使用键的最左面前缀,或如果键不是UNIQUEPRIMARY KEY(换句话说,如果联结不能基于键值选择单个行的话),使用ref。如果被使用的键仅仅匹配一些行,该联结类型是不错的。
range
只有在一个给定范围的行将被检索,使用一个索引选择行。ref列显示哪个索引被使用。
index
这与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL
对于每个来自于先前的表的行组合,将要做一个完整的表扫描。如果表格是第一个没标记const的表,这通常不好,并且通常在所有的其他情况下差。你通常可以通过增加更多的索引来避免ALL,使得行能从早先的表中基于常数值或列值被检索出。

通过相乘EXPLAIN输出的rows行的所有值,你能得到一个关于一个联结要多好的提示。这应该粗略地告诉你MySQL必须检验多少行以执行查询。当你使用max_join_size变量限制查询时,也用这个数字。见10.2.3 调节服务器参数

下列例子显示出一个JOIN如何能使用EXPLAIN提供的信息逐步被优化。

假定你有显示在下面的SELECT语句,你使用EXPLAIN检验:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

对于这个例子,假定:

开始,在任何优化被施行前,EXPLAIN语句产生下列信息:

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)

因为type对每张表是ALL,这个输出显示MySQL正在对所有表进行一个完整联结!这将花相当长的时间,因为必须检验每张表的行数的乘积次数!对于一个实例,这是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大,你只能想象它将花多长时间……

如果列声明不同,这里的一个问题是MySQL(还)不能高效地在列上使用索引。在本文中,VARCHARCHAR是相同的,除非他们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15),有一个长度失配。

为了修正在列长度上的不同,使用ALTER TABLEActualPC的长度从10个字符变为15个字符:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在tt.ActualPCet.EMPLOYID都是VARCHAR(15),再执行EXPLAIN语句产生这个结果:

table type   possible_keys   key     key_len ref         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    where used
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1

这不是完美的,但是是好一些了(rows值的乘积少了一个74一个因子),这个版本在几秒内执行。

第2种改变能消除tt.AssignedPC = et_1.EMPLOYIDtt.ClientID = do.CUSTNMBR比较的列的长度失配:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

现在EXPLAIN产生的输出显示在下面:

table type   possible_keys   key     key_len ref            rows     Extra
et    ALL    PRIMARY         NULL    NULL    NULL           74
tt    ref    AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

这“几乎”象它能得到的一样好。

剩下的问题是,缺省地,MySQL假设在tt.ActualPC列的值是均匀分布的,并且对tt表不是这样。幸好,很容易告诉MySQL关于这些:

shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

现在联结是“完美”的了,而且EXPLAIN产生这个结果:

table type   possible_keys   key     key_len ref            rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL    3872    where used
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC    1
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

注意在从EXPLAIN输出的rows列是一个来自MySQL联结优化器的“教育猜测”;为了优化查询,你应该检查数字是否接近事实。如果不是,你可以通过在你的SELECT语句里面使用STRAIGHT_JOIN并且试着在在FROM子句以不同的次序列出表,可能得到更好的性能。

 

7.23 DESCRIBE句法 (得到列的信息)

{DESCRIBE | DESC} tbl_name {col_name | wild}

DESCRIBE提供关于一张表的列的信息。col_name可以是一个列名字或包含SQL的“%”“_”通配符的一个字符串。

如果列类型不同于你期望的是基于一个CREATE TABLE语句,注意MySQL有时改变列类型。见7.7.1 隐含的列说明变化

这个语句为了与 Oracle 兼容而提供的。

SHOW语句提供类似的信息。见7.21 SHOW句法(得到表,列的信息)

 

7.24 LOCK TABLES/UNLOCK TABLES句法

LOCK TABLES tbl_name [AS alias] {READ | [LOW_PRIORITY] WRITE}
            [, tbl_name {READ | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES

LOCK TABLES为当前线程锁定表。UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时,或当服务器的连接被关闭时,当前线程锁定的所有表自动被解锁。

如果一个线程获得在一个表上的一个READ锁,该线程(和所有其他线程)只能从表中读。如果一个线程获得一个表上的一个WRITE锁,那么只有持锁的线程READWRITE表,其他线程被阻止。

每个线程等待(没有超时)直到它获得它请求的所有锁。

WRITE锁通常比READ锁有更高的优先级,以确保更改尽快被处理。这意味着,如果一个线程获得READ锁,并且然后另外一个线程请求一个WRITE锁, 随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。当线程正在等待WRITE锁时,你可以使用LOW_PRIORITY WRITE允许其他线程获得READ锁。如果你肯定终于有个时刻没有线程将有一个READ锁,你应该只使用LOW_PRIORITY WRITE

当你使用LOCK TABLES时,你必须锁定你将使用的所有表!如果你正在一个查询中多次使用一张表(用别名),你必须对每个别名得到一把锁!这条政策保证表锁定不会死锁。

注意你应该锁定任何你正在用INSERT DELAYED使用的表,这是因为在这种情况下,INSERT被一个不同的线程执行。

通常,你不必锁定表,因为所有单个UPDATE语句是原语;没有其他线程能防碍任何其它正在执行SQL语句的线程。当你想锁定表,有一些情况:

没有LOCK TABLES,另外一个线程可能有一个机会在执行SELECTUPDATE语句之间往trans表中插入一个新行。

通过使用渐增更改(UPDATE customer SET value=value+new_value)或LAST_INSERT_ID()函数,在很多情况下你能使用LOCK TABLES来避免。

你也可以使用用户级锁定函数GET_LOCK()RELEASE_LOCK()解决一些情况,这些锁保存在服务器的一张哈希表中并且用pthread_mutex_lock()pthread_mutex_unlock()实现以获得高速度。见7.4.12 其他函数

有关锁定政策的更多信息,见10.2.8 MySQL 怎样锁定表

7.25 SET OPTION句法

SET [OPTION] SQL_VALUE_OPTION= value, ...

SET OPTION设置影响服务器或你的客户操作的各种选项。你设置的任何选择保持有效直到当前会话结束,或直到你设置选项为不同的值。

CHARACTER SET character_set_name | DEFAULT
这用给定的映射表从/到客户映射所有字符串。对character_set_name当前唯一的选项是 cp1251_koi8,但是你能容易通过编辑在MySQL源代码分发的“sql/convert.cc”文件增加新的映射。缺省映射能用character_set_nameDEFAULT值恢复。注意设置CHARACTER SET选项的语法不同于设置其他选项目的语法。
PASSWORD = PASSWORD('some password')
设置当前用户的口令。任何非匿名的用户能改变他自己的口令!
PASSWORD FOR user = PASSWORD('some password')
设置当前服务器主机上的一个特定用户的口令。只有具备存取mysql数据库的用户可以这样做。用户应该以user@hostname格式给出,这里userhostname完全与他们列在mysql.user表条目的UserHost列一样。例如,如果你有一个条目其UserHost字段是'bob''%.loc.gov',你将写成:
mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");

或

mysql> UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' and host="%.loc.gov";
SQL_AUTO_IS_NULL = 0 | 1
如果设置为1(缺省 ),那么对于一个具有一个自动加1的行的表,用下列构件能找出最后插入的行:WHERE auto_increment_column IS NULL。这被一些 ODBC 程序入Access使用。
SQL_BIG_TABLES = 0 | 1
如果设置为1,所有临时表存在在磁盘上而非内存中。这将更慢一些,但是对需要大的临时表的大SELECT操作,你将不会得到The table tbl_name is full的错误。对于一个新连接的缺省值是0(即,使用内存中的临时表)。
SQL_BIG_SELECTS = 0 | 1
如果设置为0,如果一个SELECT尝试可能花很长的时间,MySQL将放弃。这在一个不妥当的WHERE语句发出时是有用的。一个大的查询被定义为一个将可能必须检验多于max_join_size行的SELECT。对一个新连接的缺省值是1(它将允许所有SELECT语句)。
SQL_LOW_PRIORITY_UPDATES = 0 | 1
如果设置为1,所有INSERTUPDATEDELETELOCK TABLE WRITE语句等待,直到在受影响的表上没有未解决的SELECTLOCK TABLE READ
SQL_SELECT_LIMIT = value | DEFAULT
SELECT语句返回的记录的最大数量。如果一个SELECT有一个LIMIT子句,LIMIT优先与SQL_SELECT_LIMIT值。对一个新连接的缺省值是“无限”的。如果你改变了限制,缺省值能用SQL_SELECT_LIMIT的一个DEFAULT值恢复。
SQL_LOG_OFF = 0 | 1
如果设置为1,如果客户有process权限,对该客户没有日志记载到标准的日志文件中。这不影响更新日志记录!
SQL_LOG_UPDATE = 0 | 1
如果设置为0, 如果客户有process权限,对该客户没有日志记载到更新日志中。这不影响标准日志文件!
TIMESTAMP = timestamp_value | DEFAULT
为该客户设置时间。如果你使用更新日志恢复行,这被用来得到原来的时间标记。
LAST_INSERT_ID = #
设置从LAST_INSERT_ID()返回的值。当你在更新一个表的命令中使用LAST_INSERT_ID()时,它存储在更新日志中。
INSERT_ID = #
设置当插入一个AUTO_INCREMENT值时,由INSERT命令使用的值。这主要与更新日志一起使用。

7.26 GRANTREVOKE句法

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY 'password']
        [, user_name [IDENTIFIED BY 'password'] ...]
    [WITH GRANT OPTION]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]

GRANTMySQL 3.22.11或以后版本中实现。对于更早MySQL版本,GRANT语句不做任何事情。

GRANTREVOKE命令允许系统主管在4个权限级别上授权和撤回赋予MySQL用户的权利:

全局级别
全局权限作用于一个给定服务器上的所有数据库。这些权限存储在mysql.user表中。
数据库级别
数据库权限作用于一个给定数据库的所有表。这些权限存储在mysql.dbmysql.host表中。
表级别
表权限作用于一个给定表的所有列。这些权限存储在mysql.tables_priv表中。
列级别
列权限作用于在一个给定表的单个列。这些权限存储在mysql.columns_priv表中。

对于GRANT如何工作的例子,见6.11 为MySQL增加新的用户权限

对于GRANTREVOKE语句,priv_type可以指定下列的任何一个:

ALL PRIVILEGES      FILE                RELOAD
ALTER               INDEX               SELECT
CREATE              INSERT              SHUTDOWN
DELETE              PROCESS             UPDATE
DROP                REFERENCES          USAGE

ALLALL PRIVILEGES的一个同义词,REFERENCES还没被实现,USAGE当前是“没有权限”的一个同义词。它能用在你想要创建一个没有权限用户的时候。

为了从一个用户撤回grant的权限,使用GRANT OPTION的一个priv_type值:

REVOKE GRANT OPTION ON ... FROM ...;

对于表,你能指定的唯一priv_type值是SELECTINSERTUPDATEDELETECREATEDROPGRANTINDEXALTER

对于列,你能指定的唯一priv_type值是(即,当你使用一个column_list子句时)是SELECTINSERTUPDATE

你能通过使用ON *.*语法设置全局权限,你能通过使用ON db_name.*语法设置数据库权限。如果你指定ON *并且你有一个当前数据库,你将为该数据库设置权限。(警告:如果你指定ON *而你有一个当前数据库,你将影响全局权限!)

为了容纳对任意主机的用户授予的权利,MySQL支持以user@host格式指定user_name值。如果你想要指定一个特殊字符的一个user字符串(例如“-”),或一个包含特殊字符或通配符的host字符串(例如“%”),你可以用括号括起能用户或主机名字 (例如,'test-user'@'test-hostname')。

你能在主机名中指定通配符。例如,user@"%.loc.gov"适用于在loc.gov域中任何主机的user,并且user@"144.155.166.%"适用于在144.155.166类 C 子网中任何主机的user

简单形式的useruser@"%"的一个同义词。注意:如果你允许匿名用户连接MySQL服务器(它是缺省的),你也应该增加所有本地用户如user@localhost,因为否则,当用户试图从本地机器上登录到MySQL服务器时,对于mysql.user表中的本地主机的匿名用户条目将被使用!匿名用户通过插入有User=''的条目到mysql.user表中来定义。通过执行这个查询,你可以检验它是否作用于你:

mysql> SELECT Host,User FROM mysql.user WHERE User='';

目前,GRANT仅支持最长60个字符的主机、表、数据库和列名。一个用户名字能最多到16个字符。

对与一个表或列的权限是由4个权限级别的逻辑或形成的。例如,如果mysql.user表指定一个用户有一个全局select权限,它不能被数据库、表或列的一个条目否认。

对于一个列的权限能如下计算:

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges

在大多数情况下,你只授予用户一个权限级别上的权限,因此现实通常不象上面所说的那样复杂。:) 权限检查过程的细节在6 MySQL 存取权限系统中给出。

如果你为一个在mysql.user表中不存在的用户/主机名组合授权,一个条目被增加并且保留直到用一个DELETE命令删除。换句话说,GRANT可以创建user表的条目,但是REVOKE将不删除;你必须明确地使用DELETE删除.

MySQL 3.22.12或以后,如果创建一个新用户或如果你有全局授予权限,用户的口令将被设置为由IDENTIFIED BY子句指定的口令,如果给出一个。如果用户已经有了一个口令,它被一个新的代替。

警告:如果你创造一个新用户但是不指定一个IDENTIFIED BY子句,用户没有口令。这是不安全的。

口令也能用SET PASSWORD命令设置。见7.25 SET OPTION句法

如果你为一个数据库授权,如果需要在mysql.db表中创建一个条目。当所有为数据库的授权用REVOKE删除时,这个条目被删除。

如果一个用户没有在一个表上的任何权限,当用户请求一系列表时,表不被显示(例如,用一个SHOW TABLES语句)。

WITH GRANT OPTION子句给与用户有授予其他用户在指定的权限水平上的任何权限的能力。你应该谨慎对待你授予他grant权限的用户,因为具有不同权限的两个用户也许能合并权限!

你不能授予其他用户你自己不具备的权限; agree权限允许你放弃你仅仅拥有的那些权限。

要知道,当你将一个特定权限级别上的grant授予其他用户,用户已经拥有(或在未来被授予!)的在该级别上的任何权限也可由该用户授权。假定你授权一个用户在一个数据库上的insert权限,那么如果你授权在数据库上select权限并且指定WITH GRANT OPTION,用户能不仅放弃select权限,还有insert。如果你授权用户在数据库上的update权限,用户能放弃insertselectupdate

你不应该将alter权限授予一个一般用户。如果你这样做,用户可以通过重命名表试图颠覆权限系统!

注意,如果你正在使用即使一个用户的表或列的权限,服务器要检查所有用户的表和列权限并且这将使MySQL慢下来一点。

mysqld启动时,所有的权限被读入存储器。数据库、表和列权限马上生效,而用户级权限在下一次用户连接时生效。你用GRANTREVOKE对受权表执行的更改立即被服务器知晓。如果你手工修改授权表(使用INSERT、UPDATE等等),你应该执行一个FLUSH PRIVILEGES语句或运行mysqladmin flush-privileges告诉服务器再次装载授权表。见6.9 权限变化何时生效

ANSI SQL版本的GRANTMySQL版本之间的最大差别:

 

7.27 CREATE INDEX句法

CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length)],... )

CREATE INDEX语句在MySQL版本 3.22 以前不做任何事情。在 3.22 或以后版本中,CREATE INDEX被映射到一个ALTER TABLE语句来创建索引。见7.8 ALTER TABLE句法

通常,你在用CREATE TABLE创建表本身时创建表的所有索引。见7.7 CREATE TABLE句法CREATE INDEX允许你把索引加到现有表中。

一个(col1,col2,...)形式的列表创造一个多列索引。索引值有给定列的值串联而成。

对于CHARVARCHAR列,索引可以只用一个列的部分来创建,使用col_name(length)句法。(在BLOBTEXT列上需要长度)。下面显示的语句使用name列的头10个字符创建一个索引:

mysql> CREATE INDEX part_of_name ON customer (name(10));
因为大多数名字通常在头10个字符不同,这个索引应该不比从整个name列的创建的索引慢多少。另外,在索引使用部分的列能使这个索引成为更小的文件大部分, 它能保存很多磁盘空格并且可能也加快INSERT操作! 

注意,如果你正在使用MySQL版本 3.23.2 或更新并且正在使用MyISAM表类型,你只能在可以有NULL值的列或一个BLOB/TEXT列上增加一个索引,

关于MySQL如何使用索引的更多信息,见10.4 MySQL索引的使用

 

7.28 DROP INDEX句法

DROP INDEX index_name ON tbl_name

DROP INDEXtbl_name表抛弃名为index_name的索引。DROP INDEXMySQL 3.22 以前的版本中不做任何事情。在 3.22 或以后,DROP INDEX被映射到一个ALTER TABLE语句来抛弃索引。见7.8 ALTER TABLE句法

7.29 注释句法

MySQL服务器支持# to end of line-- to end of line/* in-line or multiple-line */注释风格:

mysql> select 1+1;     # This comment continues to the end of line
mysql> select 1+1;     -- This comment continues to the end of line 
mysql> select 1 /* this is an in-line comment */ + 1;
mysql> select 1+
/*
this is a
multiple-line comment
*/
1;

注意--注释风格要求你在--以后至少有一个空格!

尽管服务者理解刚才描述的注释句法,mysql客户分析/* ... */注释的方式上有一些限制:

当你交互式运行mysql时和当你把命令放在一个文件中并用mysql < some-file告诉mysql从那个文件读它的输入时,这些限制都适用。

MySQL不支持‘--’的ANSI SQL注释风格。见5.4.7 ‘--’作为一篇注释的开始

 

7.30 CREATE FUNCTION/DROP FUNCTION句法

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|REAL|INTEGER}
       SONAME shared_library_name

DROP FUNCTION function_name

一个用户可定义函数(UDF)是用一个像MySQL的原生(内置)函数如ABS()CONCAT()的新函数来扩展MySQL的方法。

AGGREGATEMySQL 3.23的一个新选项。一个AGGREGATE函数功能就像一个原生MySQL GROUP函数如SUMCOUNT()

CREATE FUNCTIONmysql.func系统表中保存函数名、类型和共享库名。你必须对mysql数据库有insertdelete权限以创建和抛弃函数。

所有活跃的函数在每次服务器启动时被重新装载,除非你使用--skip-grant-tables选项启动mysqld,在这种情况下,UDF初始化被跳过并且UDF是无法获得的。(一个活跃函数是一个已经用CREATE FUNCTION装载并且没用DROP FUNCTION删除的函数。)

关于编写用户可定义函数的指令,见14 为MySQL增加新函数。对于UDF的工作机制,函数必须用 C 或 C++ 编写,你的操作系统必须支持动态装载并且你必须动态编译了mysqld(不是静态)。

 

7.31 MySQL对保留词很挑剔吗?

一个常见的问题源自于试图使用MySQL内置的数据类型或函数名同名的列来创建数据库表,例如TIMESTAMPGROUP。你允许这样做(例如,ABS是一个允许的列名),但是当使用其名字也是列名的函数时,在一个函数名和“ ( ”之间不允许白空。

下列词明确地在MySQL中被保留。他们的大多数被 ANSI SQL92 禁止作为列或表名(例如,group)。一些被保留因为MySQL需要他们并且正在(当前)使用一个yacc分析器:

action add aggregate all
alter after and as
asc avg avg_row_length auto_increment
between bigint bit binary
blob bool both by
cascade case char character
change check checksum column
columns comment constraint create
cross current_date current_time current_timestamp
data database databases date
datetime day day_hour day_minute
day_second dayofmonth dayofweek dayofyear
dec decimal default delayed
delay_key_write delete desc describe
distinct distinctrow double drop
end else escape escaped
enclosed enum explain exists
fields file first float
float4 float8 flush foreign
from for full function
global grant grants group
having heap high_priority hour
hour_minute hour_second hosts identified
ignore in index infile
inner insert insert_id int
integer interval int1 int2
int3 int4 int8 into
if is isam join
key keys kill last_insert_id
leading left length like
lines limit load local
lock logs long longblob
longtext low_priority max max_rows
match mediumblob mediumtext mediumint
middleint min_rows minute minute_second
modify month monthname myisam
natural numeric no not
null on optimize option
optionally or order outer
outfile pack_keys partial password
precision primary procedure process
processlist privileges read real
references reload regexp rename
replace restrict returns revoke
rlike row rows second
select set show shutdown
smallint soname sql_big_tables sql_big_selects
sql_low_priority_updates sql_log_off sql_log_update sql_select_limit
sql_small_result sql_big_result sql_warnings straight_join
starting status string table
tables temporary terminated text
then time timestamp tinyblob
tinytext tinyint trailing to
type use using unique
unlock unsigned update usage
values varchar variables varying
varbinary with write when
where year year_month zerofill

下列符号(来自上表)被ANSI SQL禁止但是被MySQL允许作为列/表名。这是因为这些名字的一些是很自然的名字并且很多人已经使用了他们。


第一章, 前一章, 下一章, 最后一章目录.