MySQL语言参考
MySQL中文参考手册
译者:晏子 (clyan@sohu.com) 主页: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的先前版本,命名规则如下:
- 一个名字可以包含来自当前字符集的数字字母的字符和“_”和“$”。缺省字符集是ISO-8859-1 Latin1;这可以通过重新编译MySQL来改变。见9.1.1 用于数据和排序的字符集。
- 一个名字可以以在一个名字中合法的任何字符开始。特别地,一个名字可以以一个数字开始(这不同于许多其他的数据库系统!)。然而,一个名字不能仅仅由数字组成。
- 你不能在名字中使用“.”,因为它被用来扩充格式,你能用它引用列(见下面)。
建议你不使用象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_name
或db_name.tbl_name
前缀,除非引用会有二义性。例如,假定表t1
和t2
,每个均包含列c
,并且你用一个使用t1
和t2
的SELECT
语句检索c
。在这种情况下,c
有二义性,因为它在使用表的语句中不是唯一的,因此你必须通过写出t1.c
或t2.c
来指明你想要哪个表。同样,如果你从数据库db1
中一个表t
和在数据库db2
的一个表t
检索,你必须用db1.t.col_name
和db2.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;
列名在所有情况下都是忽略大小写的。
表的别名是区分大小写的。下列查询将不工作,
: 因为它用a
和A
引用别名:
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。
方括号(“[”和“]”)指出可选的类型修饰符的部分。
注意,如果你指定一个了为ZEROFILL
,MySQL将为该列自动地增加UNSIGNED
属性。
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
- 一个很小的整数。有符号的范围是
-128
到127
,无符号的范围是0
到255
。 SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
- 一个小整数。有符号的范围是
-32768
到32767
,无符号的范围是0
到65535
。 MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
- 一个中等大小整数。有符号的范围是
-8388608
到8388607
,无符号的范围是0
到16777215
。 INT[(M)] [UNSIGNED] [ZEROFILL]
- 一个正常大小整数。有符号的范围是
-2147483648
到2147483647
,无符号的范围是0
到4294967295
。 INTEGER[(M)] [UNSIGNED] [ZEROFILL]
- 这是
INT
的一个同义词。 BIGINT[(M)] [UNSIGNED] [ZEROFILL]
- 一个大整数。有符号的范围是
-9223372036854775808
到9223372036854775807
,无符号的范围是0
到18446744073709551615
。注意,所有算术运算用有符号的BIGINT
或DOUBLE
值完成,因此你不应该使用大于9223372036854775807
(63位)的有符号大整数,除了位函数!注意,当两个参数是INTEGER
值时,-
、+
和*
将使用BIGINT
运算!这意味着如果你乘2个大整数(或来自于返回整数的函数),如果结果大于9223372036854775807
,你可以得到意外的结果。一个浮点数字,不能是无符号的,对一个单精度浮点数,其精度可以
是<=24
,对一个双精度浮点数,是在25 和53之间,这些类型如FLOAT
和DOUBLE
类型马上在下面描述。FLOAT(X)
有对应的FLOAT
和DOUBLE
相同的范围,但是显示尺寸和小数位数是未定义的。在MySQL3.23中,这是一个真正的浮点值。在更早的MySQL版本中,FLOAT(precision)
总是有2位小数。该句法为了ODBC兼容性而提供。 FLOAT[(M,D)] [ZEROFILL]
- 一个小(单精密)浮点数字。不能无符号。允许的值是
-3.402823466E+38
到-1.175494351E-38
,0
和1.175494351E-38
到3.402823466E+38
。M是显示宽度而D是小数的位数。没有参数的FLOAT
或有<24 的一个参数表示一个单精密浮点数字。 DOUBLE[(M,D)] [ZEROFILL]
- 一个正常大小(双精密)浮点数字。不能无符号。允许的值是
-1.7976931348623157E+308
到-2.2250738585072014E-308
、0
和2.2250738585072014E-308
到1.7976931348623157E+308
。M是显示宽度而D是小数位数。没有一个参数的DOUBLE
或FLOAT(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
列,实际的范围可以通过M
和D
的选择被限制。如果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
年的某时。MySQL以YYYYMMDDHHMMSS
、YYMMDDHHMMSS
、YYYYMMDD
或YYMMDD
格式来显示TIMESTAMP
值,取决于是否M
是14
(或省略)、12
、8
或6
,但是允许你使用字符串或数字把值赋给TIMESTAMP
列。一个TIMESTAMP
列对于记录一个INSERT
或UPDATE
操作的日期和时间是有用的,因为如果你不自己给它赋值,它自动地被设置为最近操作的日期和时间。你以可以通过赋给它一个NULL
值设置它为当前的日期和时间。见7.3.6 日期和时间类型。 TIME
- 一个时间。范围是
'-838:59:59'
到'838:59:59'
。MySQL以'HH:MM:SS'
格式来显示TIME
值,但是允许你使用字符串或数字把值赋给TIME
列。 YEAR[(2|4)]
- 一个2或4位数字格式的年(缺省是4位)。允许的值是
1901
到2155
,和0000
(4位年格式),如果你使用2位,1970-2069( 70-69)。MySQL以YYYY
格式来显示YEAR
值,但是允许你把使用字符串或数字值赋给YEAR
列。(YEAR
类型在MySQL3.22中是新类型。) CHAR(M) [BINARY]
- 一个定长字符串,当存储时,总是是用空格填满右边到指定的长度。
M
的范围是1 ~ 255个字符。当值被检索时,空格尾部被删除。CHAR
值根据缺省字符集以大小写不区分的方式排序和比较,除非给出BINARY
关键词。NATIONAL CHAR
(短形式NCHAR
)是ANSI SQL的方式来定义CHAR列应该使用缺省字符集。这是MySQL
的缺省。CHAR
是CHARACTER
的一个缩写。 [NATIONAL] VARCHAR(M) [BINARY]
- 一个变长字符串。注意:当值被存储时,尾部的空格被删除(这不同于ANSI SQL规范)。M的范围是1 ~ 255个字符。
VARCHAR
值根据缺省字符集以大小写不区分的方式排序和比较,除非给出BINARY
关键词值。见7.7.1 隐式列指定变化。VARCHAR
是CHARACTER VARYING
一个缩写。 TINYBLOB
TINYTEXT
- 一个
BLOB
或TEXT
列,最大长度为255(2^8-1)个字符。见7.7.1 隐式列指定变化。 BLOB
TEXT
- 一个
BLOB
或TEXT
列,最大长度为65535(2^16-1)个字符。见7.7.1 隐式列指定变化。 MEDIUMBLOB
MEDIUMTEXT
- 一个
BLOB
或TEXT
列,最大长度为16777215(2^24-1)个字符。见7.7.1 隐式列指定变化。 LONGBLOB
LONGTEXT
- 一个
BLOB
或TEXT
列,最大长度为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 <= M 和1 <= 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个成员) |
VARCHAR
和BLOB
和TEXT
类型是变长类型,对于其存储需求取决于列值的实际长度(在前面的表格中用L
表示),而不是取决于类型的最大可能尺寸。例如,一个VARCHAR(10)
列能保存最大长度为10个字符的一个字符串,实际的存储需要是字符串的长度(L
),加上1个字节以记录字符串的长度。对于字符串'abcd'
,L
是4而存储要求是5个字节。
BLOB
和TEXT
类型需要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
)。关键词INT
是INTEGER
的一个同义词,而关键词DEC
是DECIMAL
一个同义词。
NUMERIC
和DECIMAL
类型被MySQL实现为同样的类型,这在SQL92标准允许。他们被用于保存值,该值的准确精度是极其重要的值,例如与金钱有关的数据。当声明一个类是这些类型之一时,精度和规模的能被(并且通常是)指定;例如:
salary DECIMAL(9,2)
在这个例子中,9
(precision
)代表将被用于存储值的总的小数位数,而2
(scale
)代表将被用于存储小数点后的位数。因此,在这种情况下,能被存储在salary
列中的值的范围是从-9999999.99
到9999999.99
。在ANSI/ISO SQL92中,句法DECIMAL(p)
等价于DECIMAL(p,0)
。同样,句法DECIMAL
等价于DECIMAL(p,0)
,这里实现被允许决定值p
。MySQL当前不支持DECIMAL
/NUMERIC
数据类型的这些变种形式的任一种。这一般说来不是一个严重的问题,因为这些类型的主要益处得自于明显地控制精度和规模的能力。
DECIMAL
和NUMERIC
值作为字符串存储,而不是作为二进制浮点数,以便保存那些值的小数精度。一个字符用于值的每一位、小数点(如果scale
>0)和“-”符号(对于负值)。如果scale
是0,DECIMAL
和NUMERIC
值不包含小数点或小数部分。
DECIMAL
和NUMERIC
值得最大的范围与DOUBLE
一样,但是对于一个给定的DECIMAL
或NUMERIC
列,实际的范围可由制由给定列的precision
或scale
限制。当这样的列赋给了小数点后面的位超过指定scale
所允许的位的值,该值根据scale
四舍五入。当一个DECIMAL
或NUMERIC
列被赋给了其大小超过指定(或缺省的)precision
和scale
隐含的范围的值,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个数字。用这个选项,第一个数字继续表示在字节计算的值存储需求,而第二个数字指定要被存储的和显示跟随小数点后的位数(就象DECIMAL
和NUMERIC
)。当MySQL要求为这样一个列,一个小数点后的小数位超过列指定的值,存储值时,该值被四舍五入,去掉额外的位。
REAL
和DOUBLE PRECISION
类型不接受精度说明。作为对 ANSI/ISO SQL92 标准的扩展,MySQL识别出DOUBLE
作为DOUBLE PRECISION
类型的一个同义词。与REAL
精度比用于DOUBLE PRECISION
的更小的标准要求相反,MySQL实现了两种,作为8字节双精度浮点值(当运行不是“Ansi模式”时)。为了最大的移植性,近似数字的数据值的存储所需代码应该使用没有精度或小数位数说明的FLOAT
或DOUBLE PRECISION
。
当要求在数字的列存储超出该列类型允许的范围的值时,MySQL剪切该值到范围内的正确端点值并且存储剪切后的结果值。
例如,一个INT
列的范围是-2147483648
到2147483647
。如果你试图插入-9999999999
到一个INT
列中,值被剪切到范围的低部端点,并存储-2147483648
。同样,如果你试图插入9999999999
,2147483647
被存储。
如果INT
列是UNSIGNED
,列的范围的大小是相同的,但是它的端点移到了0
和4294967295
。如果你试图存储-9999999999
和9999999999
,在列被存储的值变为0
和4294967296
。
对于ALTER TABLE
、LOAD DATA INFILE
、UPDATE
和多行INSERT
语句,由于剪切所发生的变换作为“警告”被报告。
7.3.6 日期和时间类型
日期和时间类型是DATETIME
、DATE
、TIMESTAMP
、TIME
和YEAR
。这些的每一个都有合法值的一个范围,而“零”当你指定确实不合法的值时被使用。注意,MySQL允许你存储某个“不严格地”合法的日期值,例如1999-11-31
,原因我们认为它是应用程序的责任来处理日期检查,而不是SQL服务器。为了使日期检查更“快”,MySQL仅检查月份在0-12的范围,天在0-31的范围。上述范围这样被定义是因为MySQL允许你在一个DATE
或DATETIME
列中存储日期,这里的天或月是零。这对存储你不知道准确的日期的一个生日的应用程序来说是极其有用的,在这种情况下,你简单地存储日期象1999-00-00
或1999-01-00
。(当然你不能期望从函数如DATE_SUB()
或DATE_ADD()
得到类似以这些日期的正确值)。
当用日期和时间工作时,这里是的一些要记住的一般考虑:
- MySQL对一个给定的日期或时间类型以标准的格式检索,但是它试图为你提供的值解释成许多格式(例如,当你指定一个值被赋给或与比较一个日期或时间类型时),但是只支持有在下列小节描述的格式。期望你提供合法的值,并且如果你以其他格式使用这些值,可能造成无法预料的结果。
- 尽管MySQL试图以多种格式解释值,但它总是期望日期值的年份部分在最左面,日期必须以年-月-日的顺序给出(例如,
'98-09-04'
),而不是以其他地方常用的月-日-年或日-月-年的次序(例如,'09-04-98'
、'04-09-98')。 - 如果一个值在数字的上下文环境中被使用,MySQL自动变换一个日期或时间类型值到一个数字,反过来也如此。
- 当MySQL遇到一个日期或时间类型的值超出范围或对给类型不合法(见本节的开始)时,它将该类型的值变换到“零”值。(例外的是超出范围的
TIME
值被剪切为适当的TIME
范围端点值。)下表显示对每种类型的“零”值的格式:列类型 “零”值 DATETIME
'0000-00-00 00:00:00'
DATE
'0000-00-00'
TIMESTAMP
00000000000000
(长度取决于显示尺寸)TIME
'00:00:00'
YEAR
0000
- “零”值是特殊的,但是你能使用在表中显示的值来明显地存储或引用他们。你也可以使用值
'0'
或0
做到, 这更容易写。 - 在MyODBC 2.50.12和以上版本中,由MyODBC使用的“零”日期或时间值被自动变换到
NULL
,因为ODBC不能处理这样的值。
7.3.6.1 Y2K问题和日期类型
MySQL本身Y2K安全的(见1.6 2000年一致性),但是呈交给MySQL的输入值可能不是。一个包含2位年份值的任何输入是由二义性的,因为世纪是未知的。这样的值必须被解释成4位形式,因为MySQL内部使用4位存储年份。
对于DATETIME
, DATE
, TIMESTAMP
和YEAR
类型,MySQL使用下列规则的解释二义性的年份值:
- 在范围
00-69
的年值被变换到2000-2069
。 - 在范围
70-99
的年值被变换到1970-1999
。
记得这些规则仅仅提供对于你数据的含义的合理猜测。如果MySQL使用的启发规则不产生正确的值,你应该提供无二义的包含4位年值的输入。
7.3.6.2 DATETIME
, DATE
和TIMESTAMP
类型
DATETIME
, DATE
和TIMESTAMP
类型是相关的。本节描述他们的特征,他们是如何类似的而又不同的。
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
列类型提供一种类型,你可以使用它自动地用当前的日期和时间标记INSERT
或UPDATE
的操作。如果你有多个TIMESTAMP
列,只有第一个自动更新。
自动更新第一个TIMESTAMP
列在下列任何条件下发生:
- 列没有明确地在一个
INSERT
或LOAD DATA INFILE
语句中指定。 - 列没有明确地在一个
UPDATE
语句中指定且一些另外的列改变值。(注意一个UPDATE
设置一个列为它已经有的值,这将不引起TIMESTAMP
列被更新,因为如果你设置一个列为它当前的值,MySQL为了效率而忽略更改。) - 你明确地设定
TIMESTAMP
列为NULL
.
除第一个以外的TIMESTAMP
列也可以设置到当前的日期和时间,只要将列设为NULL
,或NOW()
。
通过明确地设置希望的值,你可以设置任何TIMESTAMP
列为不同于当前日期和时间的值,即使对第一个TIMESTAMP
列也是这样。例如,如果,当你创建一个行时,你想要一个TIMESTAMP
被设置到当前的日期和时间,但在以后无论何时行被更新时都不改变,你可以使用这个属性:
- 让MySQL在行被创建时设置列,这将初始化它为当前的日期和时间。
- 当你执行随后的对该行中其他列的更改时,明确设定
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范围的奇数值尺寸被强制为下一个更大的偶数。
使用一个常用的格式集的任何一个,你可以指定DATETIME
、DATE
和TIMESTAMP
值:
'YYYY-MM-DD HH:MM:SS'
或'YY-MM-DD HH:MM:SS'
格式的一个字符串。允许一种“宽松”的语法--任何标点可用作在日期部分和时间部分之间的分隔符。例如,'98-12-31 11:30:45'
、'98.12.31 11+30+45'
、'98/12/31 11*30*45'
和'98@12@31 11^30^45'
是等价的。'YYYY-MM-DD'
或'YY-MM-DD'
格式的一个字符串。允许一种“宽松”的语法。例如,'98-12-31'
,'98.12.31'
,'98/12/31'
和'98@12@31'
是等价的。'YYYYMMDDHHMMSS'
或'YYMMDDHHMMSS'
格式的没有任何分隔符的一个字符串,例如,'19970523091528'
和'970523091528'
被解释为'1997-05-23 09:15:28'
,但是'971122459015'
是不合法的(它有毫无意义的分钟部分)且变成'0000-00-00 00:00:00'
。'YYYYMMDD'
或'YYMMDD'
格式的没有任何分隔符的一个字符串,如果字符串认为是一个日期。例如,'19970523'
和'970523'
被解释作为'1997-05-23'
,但是'971332'
是不合法的( 它有无意义的月和天部分)且变成'0000-00-00'
。YYYYMMDDHHMMSS
或YYMMDDHHMMSS
格式的一个数字,如果数字认为是一个日期。例如,19830905132800
和830905132800
被解释作为'1983-09-05 13:28:00'
。YYYYMMDD
或YYMMDD
格式的一个数字,如果数字认为是一个日期。例如,19830905
和830905
被解释作为'1983-09-05'
。- 一个返回值可以在一个
DATETIME
,DATE
或TIMESTAMP
上下文环境中接受的函数,例如NOW()
或CURRENT_DATE
。
不合法DATETIME
, DATE
或TIMESTAMP
值被变换到适当类型的“零”值('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位长,它被假定以YYYYMMDD
或YYYYMMDDHHMMSS
格式并且年份由头4位数字给出。如果数字是6或12位长,它被假定是以YYMMDD
或YYMMDDHHMMSS
格式且年份由头2位数字给出。不是这些长度之一的数字通过填补前头的零到最接近的长度来解释。
指定为无分隔符的字符串用它们给定的长度来解释。如果字符串长度是8或14个字符,年份被假定头4个字符给出,否则年份被假定由头2个字符给出。对于字符串中呈现的多个部分,字符串从左到右边被解释,以找出年、月、日、小时、分钟和秒值,这意味着,你不应该使用少于 6 个字符的字符串。例如,如果你指定'9903'
,认为将代表1999年3月,你会发现MySQL把一个“零”日期插入到你的表中,这是因为年份和月份值99
和03
,但是日期部分丢失(零),因此该值不是一个合法的日期。
TIMESTAMP
列使用被指定的值的完整精度的存储合法的值,不考虑显示大小。这有几个含意:
- 总是指定年,月,和日,即使你的列类型是
TIMESTAMP(4)
或TIMESTAMP(2)
。否则,值将不是一个合法的日期并且0
将被存储。 - 如果你使用
ALTER TABLE
拓宽一个狭窄的TIMESTAMP
列,以前被“隐蔽”的信息将被显示。 - 同样,缩小一个
TIMESTAMP
列不会导致信息失去,除了感觉上值在显示时,较少的信息被显示出。 - 尽管
TIMESTAMP
值被存储为完整精度,直接操作存储值的唯一函数是UNIX_TIMESTAMP()
,其他函数操作在格式化了的检索的值上,这意味着你不能使用函数例如HOUR()
或SECOND()
,除非TIMESTAMP
值的相关部分被包含在格式化的值中。例如,一个TIMESTAMP
列的HH
部分部被显示,除非显示大小至少是10,因此在更短的TIMESTAMP
值上试试使用HOUR()
产生一个无意义的结果。
在某种程度上,你可以把一种日期类型的值赋给一个不同的日期类型的对象。然而,这可能值有一些改变或信息的损失:
- 如果你将一个
DATE
值赋给一个DATETIME
或TIMESTAMP
对象,结果值的时间部分被设置为'00:00:00'
,因为DATE
值不包含时间信息。 - 如果你将一个
DATETIME
或TIMESTAMP
值赋给一个DATE
对象,结果值的时间部分被删除,因为DATE
类型不存储时间信息。 - 记住,尽管
DATETIME
,DATE
和TIMESTAMP
值全都可以用同样的格式集来指定,但所有类型不都有同样的值范围。例如,TIMESTAMP
值不能比1970
早或比2037
网晚,这意味着,一个日期例如'1968-01-01'
,当作为一个DATETIME
或DATE
值合法时,它不是一个正确TIMESTAMP
值,并且如果赋值给这样一个对象,它将被变换到0
。
当指定日期值时,当心某些缺陷:
- 允许作为字符串指定值的宽松格式能被欺骗。例如,值例如
'10:11:12'
可能看起来像时间值,因为“:”分隔符,但是如果在一个日期中使用,上下文将作为年份被解释成'2010-11-12'
。值'10:45:15'
将被变换到'0000-00-00'
,因为'45'
不是一个合法的月份。 - 以2位数字指定的年值是模糊的,因为世纪是未知的。MySQL使用下列规则解释2位年值:
- 在
00-69
范围的年值被变换到2000-2069
。 - 在范
70-99
围的年值被变换到1970-1999
。
- 在
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
值:
- 作为
'HH:MM:SS'
格式的一个字符串。“宽松”的语法被允许--任何标点符号可用作时间部分的分隔符,例如,'10:11:12'
和'10.11.12'
是等价的。 - 作为没有分隔符的
'HHMMSS'
格式的一个字符串,如果它作为一个时间解释。例如,'101112'
被理解为'10:11:12'
,但是'109712'
是不合法的(它有无意义的分钟部分)并变成'00:00:00'
。 - 作为
HHMMSS
格式的一个数字,如果它能解释为一个时间。例如,101112
被理解为'10:11:12'
。 - 返回值可在一个
TIME
上下文接受的函数,例如CURRENT_TIME
。
对于作为包括一个时间分隔符的字符串被指定的TIME
值,不必为小于10
的小时、分钟或秒值指定2位数字,'8:3:2'
与'08:03:02'
是一样的。
将“短的”TIME
值赋值给一个TIME
行列是要格外小心。MySQL使用最右位代表秒的假设来解释值。(MySQL将TIME
值解释为经过的时间,而非作为一天的时间 )例如,你可能想到'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
值,其范围是1901
到2155
。
你能用多种格式指定YEAR
值:
- 作为在
'1901'
到'2155'
范围的一个4位字符串。 - 作为在
1901
到2155
范围的一个4位数字。 - 作为在
'00'
到'99'
范围的一个2位字符串.在'00'
到'69'
和'70'
到'99'
范围的值被变换到在2000
到2069
范围和1970
到1999
的YEAR
值。 - 作为在
1
到99
范围的一个2位数字。在范围1
到69
和70
到99
的值被变换到在范围2001
到2069
和1970
到1999
的YEAR
的值。注意对于2位数字的范围略微不同于2位数字字符串的范围,因为你不能直接指定零作为一个数字并且把它解释为2000
。你必须作为一个字符串'0'
或'00'
指定它,它将被解释为0000
。 - 其返回值可在一个
YEAR
上下文环境中接受的函数,例如NOW()
。
不合法YEAR
值被变换到0000
。
7.3.7 字符串类型
字符串类型是CHAR
、VARCHAR
、BLOB
、TEXT
、ENUM
和SET
。
7.3.7.1 CHAR
和VARCHAR
类型
CHAR
和VARCHAR
类型是类似的,但是在他们被存储和检索的方式不同。
一个CHAR
列的长度被修正为在你创造表时你所声明的长度。长度可以是1和255之间的任何值。(在MySQL 3.23中,CHAR
长度可以是0~255。) 当CHAR
值被存储时,他们被用空格在右边填补到指定的长度。当CHAR
值被检索时,拖后的空格被删去。
在VARCHAR
列中的值是变长字符串。你可以声明一个VARCHAR
列是在1和255之间的任何长度,就像对CHAR
列。然而,与CHAR
相反,VARCHAR
值只存储所需的字符,外加一个字节记录长度,值不被填补;相反,当值被存储时,拖后的空格被删去。(这个空格删除不同于ANSI SQL规范。)
如果你把一个超过列最大长度的值赋给一个CHAR
或VARCHAR
列,值被截断以适合它。
下表显示了两种类型的列的不同,通过演示存储变长字符串值到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
列上被删除。
在CHAR
和VARCHAR
列中存储和比较值是以大小写不区分的方式进行的,除非当桌子被创建时,BINARY
属性被指定。BINARY
属性意味着该列的值根据MySQL服务器正在运行的机器的ASCII顺序以大小写区分的方式存储和比较。
BINARY
属性是“粘性”的。这意味着,如果标记了BINARY
的列用于一个表达式中,整个的表达式作为一个BINARY
值被比较。
MySQL在表创建时可以隐含地改变一个CHAR
或VARCHAR
列的类型。见7.7.1 隐含的的列说明改变。
7.3.7.2 BLOB
和TEXT
类型
一个BLOB
是一个能保存可变数量的数据的二进制的大对象。4个BLOB
类型TINYBLOB
、BLOB
、MEDIUMBLOB
和LONGBLOB
仅仅在他们能保存值的最大长度方面有所不同。见7.3.1 列类型存储需求。
4个TEXT
类型TINYTEXT
、TEXT
、MEDIUMTEXT
和LONGTEXT
对应于4个BLOB
类型,并且有同样的最大长度和存储需求。在BLOB
和TEXT
类型之间的唯一差别是对BLOB
值的排序和比较以大小写敏感方式执行,而对TEXT
值是大小写不敏感的。换句话说,一个TEXT
是一个大小写不敏感的BLOB
。
如果你把一个超过列类型最大长度的值赋给一个BLOB
或TEXT
列,值被截断以适合它。
在大多数方面,你可以认为一个TEXT
行列是你所希望大的一个VARCHAR
列。同样,你可以认为一个BLOB
列是一个VARCHAR BINARY
列。差别是:
- 用MySQL版本3.23.2和更新,你能在
BLOB
和TEXT
列上索引。更旧的MySQL版本不支持这个。 - 当值被存储时,对
BLOB
和TEXT
列没有拖后空格的删除,因为对VARCHAR
列有删除。 BLOB
和TEXT
列不能有DEFAULT
值。
MyODBC定义BLOB
为LONGVARBINARY
,TEXT
值为LONGVARCHAR
。
因为BLOB
和TEXT
值可以是非常长的,当使用他们时,你可能遇到一些限制:
- 如果你想要在一个
BLOB
或TEXT
列上使用GROUP BY
或ORDER BY
,你必须将列值变换成一个定长对象。这样做的标准方法是用SUBSTRING
函数。例如:mysql> select comment from tbl_name,substring(comment,20) as substr ORDER BY substr;
如果你不这样做,在排序时,只有列的首
max_sort_length
个字节被使用,缺省的max_sort_length
是1024;这个值能在启动mysqld
服务器时使用-O
选择改变。你可以在包含BLOB
或TEXT
值得一个表达式上分组(group),通过指定列的位置或使用一个别名:mysql> select id,substring(blob_col,1,100) from tbl_name GROUP BY 2; mysql> select id,substring(blob_col,1,100) as b from tbl_name GROUP BY b;
- 一个
BLOB
或TEXT
对象的最大尺寸由其类型决定,但是你能在客户与服务器之间是实际传输的最大值由可用的内存数量和通讯缓冲区的大小来决定。你能改变消息缓冲区大小,但是你必须在服务器和客户两端做。见10.2.3 调节服务器参数。
注意,每个BLOB
或TEXT
值内部由一个独立分配的对象表示。这与所有的其他列类型相反,它们是在打开表时,按列被分配一次存储。
7.3.7.3 ENUM
类型
一个ENUM
是一个字符对象,其值通常从一个在表创建时明确被列举的允许值的一张表中选择。
在下列的某个情形下,值也可以空字符串(""
)或NULL
:
- 如果你把一个无效值插入到一个
ENUM
(即,一个不在允许的值列表中的字符串),空字符串作为一个特殊错误的值被插入。 - 如果一个
ENUM
被声明为NULL
,NULL
也是列的合法值,并且缺省值是NULL
。如果一个ENUM
被声明为NOT NULL
,缺省值是允许值的列表的第一成员。
每枚举值有一个编号:
- 在列说明中来自允许成员值列表值用从1开始编号。
- 空字符串错误值的编号值是0。这意味着,你能使用下列
SELECT
语句找出被赋给无效ENUM
值的行:mysql> SELECT * FROM tbl_name WHERE enum_col=0;
NULL
值的编号是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 为列选择正确的类型
为了最有效地使用存储空间,试着在所有的情况下使用最精确的类型。例如,如果一个整数列被用于在之间1
和99999
的值, MEDIUMINT UNSIGNED
是最好的类型。
货币值的精确表示是一个常见的问题。在MySQL,你应该使用DECIMAL
类型,它作为一个字符串被存储,不会发生精确性的损失。如果精确性不是太重要,DOUBLE
类型也是足够好的。
对高精度,你总是能变换到以一个BIGINT
存储的定点类型。这允许你用整数做所有的计算,并且仅在必要时将结果转换回浮点值。见10.6 选择一个表类型。
7.3.9 列索引
所有的MySQL列类型能被索引。在相关的列上的使用索引是改进SELECT
操作性能的最好方法。
一个表最多可有16个索引。最大索引长度是256个字节,尽管这可以在编译MySQL时被改变。
对于CHAR
和VARCHAR
列,你可以索引列的前缀。这更快并且比索引整个列需要较少的磁盘空间。在CREATE TABLE
语句中索引列前缀的语法看起来像这样:
KEY index_name (col_name(length))
下面的例子为name
列的头10个字符创建一个索引:
mysql> CREATE TABLE test ( name CHAR(200) NOT NULL, KEY index_name (name(10)));
对于BLOB
和TEXT
列,你必须索引列的前缀,你不能索引列的全部。
7.3.10 多列索引
MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。(在CHAR
和VARCHAR
列上,你也可以使用列的前缀作为一个索引的部分)。
一个多重列索引可以认为是包含通过合并(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_name
和first_name
上的索引,这个索引将被用于在last_name
或last_name
和first_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 用在SELECT
和WHERE
子句中的函数
在一个SQL语句中的select_expression
或where_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 分组函数
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
/
- 除法
mysql> select 3/5; -> 0.60
被零除产生一个
NULL
结果:mysql> select 102/(1-1); -> NULL
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
&&
- 逻辑与。如果任何一个参数是
0
或NULL
,返回0
,否则返回1
。mysql> select 1 && NULL; -> 0 mysql> select 1 && 0; -> 0
7.4.5 比较运算符
比较操作得出值1
(TRUE)、0
(FALSE)或NULL
等结果。这些函数工作运用在数字和字符串上。当需要时,字符串自动地被变换到数字且数字到字符串(如在Perl)。
MySQL使用下列规则执行比较:
- 如果一个或两个参数是
NULL
,比较的结果是NULL
,除了<=>
操作符。 - 如果在比较中操作的两个参数是字符串,他们作为字符串被比较。
- 如果两个参数是整数,他们作为整数被比较。
- 十六进制的值如果不与一个数字比较,则被当作二进制字符串。
- 如果参数之一是一个
TIMESTAMP
或DATETIME
列而其他参数是一个常数,在比较执行前,常数被转换为一个时间标记。这样做是为了对ODBC更友好。 - 在所有其他的情况下,参数作为浮点(实数)数字被比较。
缺省地,字符串使用当前的字符集以大小写敏感的方式进行(缺省为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
对大于或等于min
且expr
是小于或等于max
,BETWEEN
返回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)
- 如果
expr
是NULL
,ISNULL()
返回1
,否则它返回0
。mysql> select ISNULL(1+1); -> 0 mysql> select ISNULL(1/0); -> 1
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 SQLLIKE
的一个扩充。)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
,否则返回0
。RLIKE
是REGEXP
的一个同义词,提供了与mSQL
的兼容性。注意:因为MySQL在字符串中使用C转义语法(例如,“\n”), 你必须在你的REGEXP
字符串重复任何“\”。在MySQL
3.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
- 当决定一个字符的类型时,
REGEXP
和RLIKE
使用当前的字符集(缺省为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
操作符强制跟随它后面的字符串为一个二进制字符串。即使列没被定义为BINARY
或BLOB
,这是一个强制列比较区分大小写的简易方法。mysql> select "a" = "A"; -> 1 mysql> select BINARY "a" = "A"; -> 0
BINARY
在MySQL 3.23.0中被引入。
7.4.8 控制流函数
IFNULL(expr1,expr2)
- 如果
expr1
不是NULL
,IFNULL()
返回expr1
,否则它返回expr2
。IFNULL()
返回一个数字或字符串值,取决于它被使用的上下文环境。 IF(expr1,expr2,expr3)
- 如果
expr1
是TRUE(expr1<>0
且expr1<>NULL
),那么IF()
返回expr2
,否则它返回expr3
。IF()
返回一个数字或字符串值,取决于它被使用的上下文。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
SIGN(X)
- 返回参数的符号,为
-1
、0
或1
,取决于X
是否是负数、零或正数。mysql> select SIGN(-32); -> -1 mysql> select SIGN(0); -> 0 mysql> select SIGN(234); -> 1
MOD(N,M)
%
- 模 (类似C中的
%
操作符)。返回N
被M
除的余数。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
ROUND(X)
- 返回参数
X
的四舍五入的一个整数。mysql> select ROUND(-1.23); -> -1 mysql> select ROUND(-1.58); -> -2 mysql> select ROUND(1.58); -> 2
ROUND(X,D)
- 返回参数
X
的四舍五入的有D
为小数的一个数字。如果D
为0
,结果将没有小数点或小数部分。mysql> select ROUND(1.298, 1); -> 1.3 mysql> select ROUND(1.298, 0); -> 1
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
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)
- 返回值
X
的Y
次幂。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
不在-1
到1
的范围,返回NULL
。mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
- 返回
X
反正弦值,即其正弦值是X
。L
如果X
不在-1
到1
的范围,返回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个变量
X
和Y
的反正切。它类似于计算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)
- 返回在范围
0
到1.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个以上的参数,返回最小(最小值)的参数。参数使用下列规则进行比较:
- 如果返回值被使用在一个
INTEGER
上下文,或所有的参数都是整数值,他们作为整数比较。 - 如果返回值被使用在一个
REAL
上下文,或所有的参数是实数值,他们作为实数比较。 - 如果任何参数是一个大小敏感的字符串,参数作为大小写敏感的字符串被比较。
- 在其他的情况下,参数作为大小写无关的字符串被比较。
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"
- 如果返回值被使用在一个
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
位小数。如果D
为0
,结果将没有小数点或小数部分。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
。如果str
是NULL
,返回NULL
。mysql> select ASCII('2'); -> 50 mysql> select ASCII(2); -> 50 mysql> select ASCII('dx'); -> 100
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)
。如果N
是NULL
,返回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)
。如果N
是NULL
,返回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
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
填补直到str
是len
个字符长。mysql> select LPAD('hi',4,'??'); -> '??hi'
RPAD(str,len,padstr)
- 返回字符串
str
,右面用字符串padstr
填补直到str
是len
个字符长。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
前缀或后缀被删除了。如果没有修饰符BOTH
、LEADING
或TRAILING
给出,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)
- 返回由重复
count
Times次的字符串str
组成的一个字符串。如果count <= 0
,返回一个空字符串。如果str
或count
是NULL
,返回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
或大于参数个数,返回NULL
。ELT()
是FIELD()
反运算。mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo'); -> 'ej' mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo'); -> 'foo'
FIELD(str,str1,str2,str3,...)
- 返回
str
在str1
,str2
,str3
,...
清单的索引。如果str
没找到,返回0
。FIELD()
是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
之中,返回一个1
到N
的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为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
的月份中日期,在1
到31
范围内。mysql> select DAYOFMONTH('1998-02-03'); -> 3
DAYOFYEAR(date)
- 返回
date
在一年中的日数, 在1
到366
范围内。mysql> select DAYOFYEAR('1998-02-03'); -> 34
MONTH(date)
- 返回
date
的月份,范围1
到12
。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
一年中的季度,范围1
到4
。mysql> select QUARTER('98-04-01'); -> 2
WEEK(date)
WEEK(date,first)
- 对于星期天是一周的第一天的地方,有一个单个参数,返回
date
的周数,范围在0
到52
。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
的年份,范围在1000
到9999
。mysql> select YEAR('98-02-03'); -> 1998
HOUR(time)
- 返回
time
的小时,范围是0
到23
。mysql> select HOUR('10:05:03'); -> 10
MINUTE(time)
- 返回
time
的分钟,范围是0
到59
。mysql> select MINUTE('98-02-03 10:05:03'); -> 5
SECOND(time)
- 回来
time
的秒数,范围是0
到59
。mysql> select SECOND('10:05:03'); -> 3
PERIOD_ADD(P,N)
- 增加
N
个月到阶段P
(以格式YYMM
或YYYYMM
)。以格式YYYYMM
返回值。注意阶段参数P
不是日期值。mysql> select PERIOD_ADD(9801,2); -> 199803
PERIOD_DIFF(P1,P2)
- 返回在时期
P1
和P2
之间月数,P1
和P2
应该以格式YYMM
或YYYYMM
。注意,时期参数P1
和P2
不是日期值。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
是一个指定开始日期的DATETIME
或DATE
值,expr
是指定加到开始日期或从开始日期减去的间隔值一个表达式,expr
是一个字符串;它可以以一个“-”开始表示负间隔。type
是一个关键词,指明表达式应该如何被解释。EXTRACT(type FROM date)
函数从日期中返回“type”间隔。下表显示了type
和expr
参数怎样被关联: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"
MySQL在
expr
格式中允许任何标点分隔符。表示显示的是建议的分隔符。如果date
参数是一个DATE
值并且你的计算仅仅包含YEAR
、MONTH
和DAY
部分(即,没有时间部分),结果是一个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假设你省掉了间隔值的最左面部分。例如,如果你指定一个type
是DAY_SECOND
,值expr
被希望有天、小时、分钟和秒部分。如果你象"1:10"
这样指定值,MySQL假设日子和小时部分是丢失的并且值代表分钟和秒。换句话说,"1:10" DAY_SECOND
以它等价于"1:10" MINUTE_SECOND
的方式解释,这对那MySQL解释TIME
值表示经过的时间而非作为一天的时间的方式有二义性。如果你使用确实不正确的日期,结果是NULL
。如果你增加MONTH
、YEAR_MONTH
或YEAR
并且结果日期大于新月份的最大值天数,日子在新月用最大的天调整。mysql> select DATE_ADD('1998-01-30', Interval 1 month); -> 1998-02-28
注意,从前面的例子中词
INTERVAL
和type
关键词不是区分大小写的。 TO_DAYS(date)
- 给出一个日期
date
,返回一个天数(从0年的天数)。mysql> select TO_DAYS(950501); -> 728779 mysql> select TO_DAYS('1997-10-07'); -> 729669
FROM_DAYS(N)
- 给出一个天数
N
,返回一个DATE
值。mysql> select FROM_DAYS(729669); -> '1997-10-07'
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
AM
或PM
%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'
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
或以YYMMDD
或YYYYMMDD
格式的本地时间的一个数字。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'
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()
系统调用加密str
。salt
参数应该是一个有2个字符的字符串。(MySQL 3.22.16中,salt
可以长于2个字符。)mysql> select ENCRYPT("hello"); -> 'VxuFAJXVARROc'
如果
crypt()
在你的系统上不可用,ENCRYPT()
总是返回NULL
。ENCRYPT()
只保留str
起始8个字符而忽略所有其他,至少在某些系统上是这样。这将由底层的crypt()
系统调用的行为决定。 ENCODE(str,pass_str)
- 使用
pass_str
作为口令加密str
。为了解密结果,使用DECODE()
。结果是一个二进制字符串,如果你想要在列中保存它,使用一个BLOB
列类型。 DECODE(crypt_str,pass_str)
- 使用
pass_str
作为口令解密加密的字符串crypt_str
。crypt_str
应该是一个由ENCODE()
返回的字符串。 MD5(string)
- 对字符串计算MD5校验和。值作为一个32长的十六进制数字被返回可以,例如用作哈希(hash)键。
mysql> select MD5("testing") -> 'ae2b1fca515949e5d54fb22b8ed95575'
LAST_INSERT_ID([expr])
- 返回被插入一个
AUTO_INCREMENT
列的最后一个自动产生的值。见20.4.29mysql_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
为小数。如果D
为0
,结果将没有小数点和小数部分。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()
函数重复count
Times次执行表达式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 BY
或ORDER BY
子句中使用表达式。你可以通过使用表达式的一个别名解决此限制:
mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name GROUP BY id,val ORDER BY val;
在MySQL
3.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 列类型。
- 如果既不指定
NULL
也不指定NOT NULL
,列被视为指定了NULL
。 - 整型列可以有附加的属性
AUTO_INCREMENT
。当你插入NULL
值(推荐)或0
到一个AUTO_INCREMENT
列中时,列被设置为value+1
,在此value
是当前表中的列的最大值。AUTO_INCREMENT
顺序从1
开始。见20.4.29mysql_insert_id()
。如果你删除了包含一个AUTO_INCREMENT
列的最大值的行,值将被重新使用。如果你删除表中所有的行,顺序重新开始。注意:每个表只能有一个AUTO_INCREMENT
列,并且它必须被索引。为了使做MySQL兼容一些 ODBC 应用程序,用下列查询你可以找出最后插入的行:SELECT * FROM tbl_name WHERE auto_col IS NULL
NULL
值对于TIMESTAMP
列的处理不同于其他列类型。你不能在一个TIMESTAMP
列中存储一个文字NULL
;设置列为NULL
将把它设成当前的日期和时间。因为TIMESTAMP
列表现就这样,NULL
和NOT NULL
属性不以一般方式运用并且如果你指定它们,将被忽略。在另一方面,为了使它MySQL客户更容易地使用TIMESTAMP
列,服务器报告这样的列可以被赋值NULL
( 它是对的),尽管TIMESTAMP
实际上绝不包含一个NULL
值。当你使用DESCRIBE tbl_name
得到有关你的表的描述时,你就会明白。注意,设置一个TIMESTAMP
列为0
不同于将它设置为NULL
,因为0
是一个有效的TIMESTAMP
值。- 如果没有为列指定
DEFAULT
值,MySQL自动地分配一个。如果列可以取NULL
作为值,缺省值是NULL
。如果列被声明为NOT NULL
,缺省值取决于列类型:- 对于没有声明
AUTO_INCREMENT
属性的数字类型,缺省值是0
。对于一个AUTO_INCREMENT
列,缺省值是在顺序中的下一个值。 - 对于除
TIMESTAMP
的日期和时间类型,缺省值是该类型适当的“零”值。对于表中第一个TIMESTAMP
列,缺省值是当前的日期和时间。见7.3.6 日期和时间类型。 - 对于除
ENUM
的字符串类型,缺省是空字符串。对于ENUM
,缺省值是第一个枚举值。
- 对于没有声明
KEY
是INDEX
的一个同义词。- 在MySQL中,一个
UNIQUE
键只能有不同的值。如果你试图用匹配现有行的键来增加新行,发生一个错误。 - A
PRIMARY KEY
是一个唯一KEY
,它有额外的限制,即所有的关键列必须被定义为NOT NULL
。在MySQL中,键被命名为PRIMARY
。一张表只能有一个PRIMARY KEY
。如果在表中你没有一个PRIMARY KEY
并且一些应用程序要求PRIMARY KEY
,MySQL将返回第一个UNIQUE
键,它没有任何NULL
列,作为PRIMARY KEY
。 - 一个
PRIMARY KEY
可以是一个多列索引。然而,你不能在一个列说明中使用PRIMARY KEY
的关键字属性创建一个多列索引。这样做将仅仅标记单个列作为主键。你必须使用PRIMARY KEY(index_col_name, ...)
语法。 - 如果你不能给索引赋予一个名字,这个索引将赋予与第一个
index_col_name
相同的名字,用一个可选的suffix(_2
,_3
,...
)使它唯一。你能使用SHOW INDEX FROM tbl_name
看到一张表的索引名字。见7.21SHOW
句法(得到表、列等的信息)。 - 只有
MyISAM
表类型支持可以有NULL
值的列上的索引。在其他情况下,你必须声明这样的列为NOT NULL
,否则导致一个错。 - 用
col_name(length)
语法,你可以指定仅使用部分的CHAR
或VARCHAR
列的一个索引。这能使索引文件变得更小。见7.3.9 列索引。 - 只有
MyISAM
表类型支持BLOB
和TEXT
列的索引。当在一个BLOB
或TEXT
列上放置索引时,你必须总是指定索引的长度:CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
- 当你与
TEXT
或BLOB
列一起使用ORDER BY
或GROUP BY
时,只使用头max_sort_length
个字节。见7.3.7.2BLOB
和TEXT
类型。 FOREIGN KEY
、CHECK
和REFERENCES
子句实际上不做任何事情,其语法仅仅提供兼容性,使得它更容易从其他的SQL服务器移植代码并运行借助引用创建表的应用。见5.4 MySQL缺少的功能。- 每个
NULL
列占据额外一位,取舍到最接近的字节。 - 最大记录长度以字节计可以如下计算:
row length = 1 + (sum of column lengths) + (number of NULL columns + 7)/8 + (number of variable-length columns)
table_options
和SELECT
选项只在MySQL 3.23和以后版本中被实现。不同的表类型是:ISAM 原来的表处理器 MyISAM 全新二进制可移植的表处理器 HEAP 用于该表的数据仅仅存储在内存中 见9.4 MySQL 表类型。其他表选项被用来优化表的行为。在大多数情况下,你不必指定他们任何一个。选项对所有表都适用,如果不是则说明。
AUTO_INCREMENT
你想要为你的表设定的下一个 auto_increment 值 ( MyISAM ) AVG_ROW_LENGTH
你的表的平均行长度的近似值。你只需要为有变长记录的表设置它。 CHECKSUM
如果你想要MySQL对每行维持一个校验和(使表变得更慢以更新但是使它更容易找出损坏的表)设置它为1 ( MyISAM ) COMMENT
对于你的表的一篇60个字符的注释 MAX_ROWS
你计划在表中存储的行的最大数目 MIN_ROWS
你计划在表中存储的行的最小数目 PACK_KEYS
如果你想要有更小的索引,将它设为1。这通常使的更新更慢并且读取更快(MyISAM,ISAM)。 PASSWORD
用一个口令加密 .frm
文件。该选项在标准MySQL版本中不做任何事情。DELAY_KEY_WRITE
如果想要推迟关键表的更新直到表被关闭(MyISAM),将它设置为1。 ROW_FORMAT
定义行应该如何被存储(为了将来)。 当你使用一个
MyISAM
表时,MySQL使用max_rows * avg_row_length
的乘积决定最终的表将有多大。如果你不指定上面的任何选项,对一个表的最大尺寸将是4G(或2G,如果你的操作系统仅支持2G的表)。- 如果你在
CREATE
语句后指定一个SELECT
,MySQL将为在SELECT
中所有的单元创键新字段。例如:mysql> CREATE TABLE test (a int not null auto_increment, primary key (a), key(b)) TYPE=HEAP SELECT b,c from test2;
这将创建一个有3个列的
HEAP
表。注意如果在拷贝数据进表时发生任何错误,表将自动被删除。
7.7.1 隐含的列说明改变
在某些情况下,MySQL隐含地改变在一个CREATE TABLE
语句给出的一个列说明。(这也可能在ALTER TABLE
。)
- 长度小于4的
VARCHAR
被改变为CHAR
。 - 如果在一个表中的任何列有可变长度,结果是整个行是变长的。因此, 如果一张表包含任何变长的列(
VARCHAR
、TEXT
或BLOB
),所有大于3个字符的CHAR
列被改变为VARCHAR
列。这在任何方面都不影响你如何使用列;在MySQL中,VARCHAR
只是存储字符的一个不同方法。MySQL实施这种改变,是因为它节省空间并且使表操作更快捷。见10.6 选择一种表格类型。 TIMESTAMP
的显示尺寸必须是偶数且在2 ~ 14的范围内。如果你指定0显示尺寸或比14大,尺寸被强制为14。从1~13范围内的奇数值尺寸被强制为下一个更大的偶数。- 你不能在一个
TIMESTAMP
列里面存储一个文字NULL
;将它设为NULL
将设置为当前的日期和时间。因为TIMESTAMP
列表现就是这样,NULL
和NOT NULL
属性不以一般的方式运用并且如果你指定他们,将被忽略。DESCRIBE tbl_name
总是报告该TIMESTAMP
列可能赋予了NULL
值。 - MySQL将其他SQL数据库供应商使用的某个列类型映射到MySQL类型。见7.3.11 只用其他数据库引擎的类型。
如果你想要知道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
改为CHAR
,MySQL将仍然使用VARCHAR
,如果表包含其他变长的列。
ALTER TABLE
通过制作原来表的一个临时副本来工作。修改在副本上施行,然后原来的表被删除并且重新命名一个新的。这样做使得所有的修改自动地转向到新表,没有任何失败的修改。当ALTER TABLE
正在执行时,原来的桌可被其他客户读取。更新和写入表被延迟到新表准备好了为止。
- 为了使用
ALTER TABLE
,你需要在表上的select、insert、delete、update、create和drop的权限。 IGNORE
是MySQL对ANSI SQL92 的一个扩充,如果在新表中的唯一键上有重复,它控制ALTER TABLE
如何工作。如果IGNORE
没被指定,副本被放弃并且恢复原状。如果IGNORE
被指定,那么对唯一键有重复的行,只有使用第一行;其余被删除。- 你可以在单个
ALTER TABLE
语句中发出多个ADD
、ALTER
、DROP
和CHANGE
子句。这是MySQL对ANSI SQL92的一个扩充,SQL92在每个ALTER TABLE
语句中只允许一个子句。 CHANGE col_name
、DROP col_name
和DROP INDEX
是MySQL对 ANSI SQL92 的扩充。MODIFY
是 Oracle 对ALTER TABLE
的扩充。- 可选的词
COLUMN
是一个纯粹的噪音且可以省略。 - 如果你使用
ALTER TABLE tbl_name RENAME AS new_name
而没有任何其他选项,MySQL简单地重命名对应于表tbl_name
的文件。没有必要创建临时表。 create_definition
子句使用CREATE TABLE
相同的ADD
和CHANGE
语法。注意语法包括列名字,不只列类型。见7.7CREATE TABLE
句法。- 你可以使用
CHANGE old_col_name create_definition
子句重命名一个列。为了这样做,指定旧的和新的列名字和列当前有的类型。例如,重命名一个INTEGER
列,从a
到b
,你可以这样做:mysql> ALTER TABLE t1 CHANGE a b INTEGER;
如果你想要改变列的类型而非名字,就算他们是一样的,
CHANGE
语法仍然需要2个列名。例如:mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
然而,在MySQL3.22.16a,你也可以使用
MODIFY
来改变列的类型而不是重命名它:mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
- 如果你使用
CHANGE
或MODIFY
缩短一个列,一个索引存在于该列的部分(例如,如果你有一个VARCHAR
列的头10个字符的索引),你不能使列短于被索引的字符数目。 - 当你使用
CHANGE
或MODIFY
改变一个列类型时,MySQL尽可能试图很好地变换数据到新类型。 - 在MySQL3.22或以后,你能使用
FIRST
或ADD ... AFTER col_name
在一个表的行内在一个特定的位置增加列。缺省是增加到最后一列。 ALTER COLUMN
为列指定新的缺省值或删除老的缺省值。如果老的缺省值被删除且列可以是NULL
,新缺省值是NULL
。如果列不能是NULL
,MySQL赋予一个缺省值。缺省值赋值在7.7CREATE TABLE
句法中描述。DROP INDEX
删除一个索引。这是MySQL对 ANSI SQL92 的一个扩充。- 如果列从一张表中被丢弃,列也从他们是组成部分的任何索引中被删除。如果组成一个索引的所有列被丢弃,该索引也被丢弃。
DROP PRIMARY KEY
丢弃主索引。如果这样的索引不存在,它丢弃表中第一个UNIQUE
索引。(如果没有明确地指定PRIMARY KEY
,MySQL标记第一个UNIQUE
键为PRIMARY KEY
。)- 用 C API 函数
mysql_info()
,你能找出多少记录被拷贝, 和(当使用IGNORE
时)由于唯一键值的重复多少记录被删除。 FOREIGN KEY
、CHECK
和REFERENCES
子句实际上不做任何事情,他们的句法仅仅提供兼容性,使得更容易地从其他SQL服务器移植代码并且运行借助引用来创建表的应用程序。见5.4 MySQL缺少的功能。
这里是一个例子,显示了一些ALTER TABLE
用法。我们以一个如下创建的表t1
开始:
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
重命名表,从t1
到t2
:
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
柱必须被索引,并且另外我们声明c
为NOT NULL
,因为索引了的列不能是NULL
。
当你增加一个AUTO_INCREMENT
列时,自动地用顺序数字填入列值。
7.9 OPTIMIZE TABLE
句法
OPTIMIZE TABLE tbl_name
如果你删除了一个表的大部分或如果你用变长的行对一个表(有VARCHAR
、BLOB
或TEXT
列的表)做了改变,应该使用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]
DELETE
从tbl_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_PRIORITY
,DELETE
的执行被推迟到没有其他客户读取表后。
删除的记录以一个链接表维持并且随后的INSERT
操作再次使用老的记录位置。为了回收闲置的空间并减小文件大小,使用OPTIMIZE TABLE
语句或myisamchk
实用程序重新组织表。OPTIMIZE TABLE
较容易,但是myisamchk
更快。见7.9 OPTIMIZE TABLE
句法和13.4.3 表优化。
MySQL对DELETE
特定的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
子句之前。
- 一个
SELECT
表达式可以用一个AS
给定一个别名,别名被用作表达式的列名并且能使用在ORDER BY
或HAVING
子句中。例如:mysql> select concat(last_name,', ',first_name) AS full_name from mytable ORDER BY full_name;
FROM table_references
子句指出从哪个表中检索行。如果你命名多个表,你正在执行一个联结(join)。对于联结的句法信息,见7.13JOIN
句法。- 你可以引用一个列为
col_name
、tbl_name.col_name
或db_name.tbl_name.col_name
,你不必在一个SELECT
语句中指定一个tbl_name
或db_name.tbl_name
是一个列引用的前缀,除非引用有二义性。见7.1.5 数据库、表、索引、列和别名命名。对于二义性的例子要求更加显式的列引用格式。 - 一个表引用可以使用
tbl_name [AS] alias_name
起一个别名。mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name; mysql> select t1.name, t2.salary from employee t1, info t2 where t1.name = t2.name;
- 精选输出的列可以用列名、列别名或列位置在
ORDER BY
和GROUP BY
子句引用,列位置从1开始。mysql> select college, region, seed from tournament ORDER BY region, seed; mysql> select college, region AS r, seed AS s from tournament ORDER BY r, s; mysql> select college, region, seed from tournament ORDER BY 2, 3;
为了以降序排列,把
DESC
(下降 )关键词加到ORDER BY
子句中你要排序的列名前。缺省是升序;这也可以用ASC
关键词明确指定。 HAVING
子句能引用任何列或在select_expression
中命名的别名,它最后运用,就在项目被送到客户之前,没有优化。不要对因该在WHERE
子句中的项目使用HAVING
。例如,不能写成这样:mysql> select col_name from tbl_name HAVING col_name > 0;
相反写成这样:
mysql> select col_name from tbl_name WHERE col_name > 0;
在MySQL 3.22.5或以后,你也能这样写查询:
mysql> select user,max(salary) from users group by user HAVING max(salary)>10;
在里面更老的MySQL版本中,你能这样写:
mysql> select user,max(salary) AS sum from users group by user HAVING sum>10;
SQL_SMALL_RESULT
、SQL_BIG_RESULT
、STRAIGHT_JOIN
和HIGH_PRIORITY
是MySQL对ANSI SQL92的扩展。STRAIGHT_JOIN
强制优化器以其列在FROM
子句的次序联结(join)桌子。如果优化器以非最佳次序联结表,你能使用它加速查询。见7.22EXPLAIN
句法(得到关于SELECT
的信息)。SQL_SMALL_RESULT
能与GROUP BY
或DISTINCT
一起使用告诉优化器结果集将很小。在这种情况下,MySQL将使用快速临时表存储最终的表而不是使用排序。SQL_SMALL_RESULT
是一个MySQL扩展。SQL_BIG_RESULT
能与GROUP BY
或DISTINCT
一起使用以告诉优化器结果集合将有很多行。在这种情况下,如果需要,MySQL将直接使用基于磁盘的临时表。MySQL
在这种情况下将选择用GROUP BY
单元上的键值进行排序而不是做一个临时表。HIGH_PRIORITY
将赋予SELECT
比一个更新表的语句更高的优先级,你应该仅对非常快的并且必须一次完成的查询使用它。 如果表为读而锁定或甚至有一个等待表释放的更新语句,一个SELECT HIGH_PRIORITY
将运行。LIMIT
子句可以被用来限制SELECT
语句返回的行数。LIMIT
取1个或2个数字参数,如果给定2个参数,第一个指定要返回的第一行的偏移量,第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
如果给定一个参数,它指出返回行的最大数目。
mysql> select * from table LIMIT 5; # Retrieve first 5 rows
换句话说,
LIMIT n
等价于LIMIT 0,n
。SELECT ... INTO OUTFILE 'file_name'
格式的SELECT
语句将选择的行写入一个文件。文件在服务器主机上被创建,并且不能是已经存在的(不管别的,这可阻止数据库表和文件例如“/etc/passwd”被破坏)。在服务器主机上你必须有file权限以使用这种SELECT
。SELECT ... INTO OUTFILE
是LOAD DATA INFILE
逆操作;语句的export_options
部分的语法与用在LOAD DATA INFILE
语句中的FIELDS
和LINES
子句的相同。见7.16LOAD DATA INFILE
句法。在最终的文本文件中,只有下列字符由ESCAPED BY
字符转义:ESCAPED BY
字符- 在
FIELDS TERMINATED BY
中的第一个字符 - 在
LINES TERMINATED BY
中的第一个字符
另外,
ASCII 0
被变换到ESCAPED BY
后跟0(ASCII 48
)。上述的原因是你必须转义任何FIELDS TERMINATED BY
、ESCAPED BY
或LINES TERMINATED BY
字符以便能可靠地能读回文件。ASCII 0
被转义使它更容易用分页器观看。因为最终的文件不必须遵循SQL句法,没有别的东西需要转义。
如果你使用INTO DUMPFILE
而不是INTO OUTFILE
,MySQL将只写一行到文件中,没有任何列或行结束并且没有任何转义。如果你想要在一个文件存储一个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兼容而存在的。
- 一个表可以是使用aliased
tbl_name AS alias_name
或tbl_name alias_name
的起的别名。mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
INNER JOIN
和,
(逗号)在语义上是等价的,都是进行一个在使用的表之间的全联结。通常,你指定表应该如何用WHERE
条件联结起来。ON
条件是可以用在一个WHERE
子句形式的任何条件。- 如果在一个
LEFT JOIN
中没有右表的匹配记录,一个所有列设置为NULL
的行被用于右表。你可以使用这个事实指出表中在另一个表中没有对应记录的记录:mysql> select table1.* from table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id is NULL;
这个例子找出在
table1
中所有的行,其id
值在table2
中不存在(即,所有table1
中的在table2
中没有对应行的行)。当然这假定table2.id
被声明为NOT NULL
。 USING
(column_list)
子句命名一系列必须存在于两个表中的列。 例如一个USING
子句:A LEFT JOIN B USING (C1,C2,C3,...)
被定义成在语义上等同一个这样的
ON
表达式:A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
- 2个表的
NATURAL LEFT JOIN
被定义为在语义上等同于一个有USING
子句命名在两表中存在的所有列的一个LEFT JOIN
。 STRAIGHT_JOIN
等同于JOIN
,除了左表在右表之前被读入,这能用于这些情况,联结优化器将表的顺序放错了。
一些例子:
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;
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 ... VALUES
或INSERT ... SELECT
不指定列表,所有列的值必须在VALUES()
表或由SELECT
提供。如果你不知道表中列的顺序,使用DESCRIBE tbl_name
来找出。 - 任何没有明确地给出值的列被设置为它的缺省值。例如,如果你指定一个列表并没命名表中所有列,未命名的列被设置为它们的缺省值。缺省值赋值在7.7
CREATE TABLE
句法中描述。 - 一个
expression
可以引用在一个值表先前设置的任何列。例如,你能这样:mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
但不能这样:
mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
- 如果你指定关键词
LOW_PRIORITY
,INSERT
的执行被推迟到没有其他客户正在读取表。在这种情况下,客户必须等到插入语句完成后,如果表频繁使用,它可能花很长时间。这与INSERT DELAYED
让客马上继续正好相反。 - 如果你在一个有许多值行的
INSERT
中指定关键词IGNORE
,表中任何复制一个现有PRIMARY
或UNIQUE
键的行被忽略并且不被插入。如果你不指定IGNORE
,插入如果有任何复制现有关键值的行被放弃。你可用C API函数mysql_info()
检查多少行被插入到表中。 - 如果MySQL用
DONT_USE_DEFAULT_FIELDS
选项配置,INSERT
语句产生一个错误,除非你明确对需要一个非NULL
值的所有列指定值。见4.7.3 典型configure
选项。 INSERT INTO ... SELECT
语句满足下列条件:- 查询不能包含一个
ORDER BY
子句。 INSERT
语句的目的表不能出现在SELECT
查询部分的FROM
子句,因为这在ANSI SQL中被禁止让从你正在插入的表中SELECT
。(问题是SELECT
将可能发现在同一个运行期间内先前被插入的记录。当使用子选择子句时,情况能很容易混淆)AUTO_INCREMENT
列象往常一样工作。
- 查询不能包含一个
如果你使用INSERT ... SELECT
或INSERT ... VALUES
语句有多个值列表,你可以使用C API函数mysql_info()
得到查询的信息。信息字符串的格式如下:
Records: 100 Duplicates: 0 Warnings: 0
Duplicates
指出不能被插入的行的数量,因为他们与现有的唯一的索引值重复。Warnings
指出在出现某些问题时尝试插入列值的次数。在下列任何条件下都可能发生错误:
- 插入
NULL
到被声明了NOT NULL
的列,列被设置为它的缺省值。 - 将超出列范围的值设置给一个数字列,值被剪切为范围内适当的端点值。
- 将数字列设成例如
'10.34 a'
的值,拖尾的垃圾被剥去并仍然是数字部分被插入。如果值根本不是一个数字,列被设置到0
。 - 把一个字符串插入到超过列的最大长度的一个
CHAR
、VARCHAR
、TEXT
或BLOB
列中。值被截断为列的最大长度。 - 把一个对列类型不合法的值插入到一个日期或时间列。列被设置为该列类型适当的“零”值。
对于INSERT
语句的DELAYED
选项是MySQL专属的选项-如果你客户有不能等到INSERT
完成,它是很有用的。当你为日记登录使用MySQL时,而且你也周期性地运行花很长时间完成的SELECT
语句,这是一个常见的问题。DELAYED
在面MySQL 3.22.15中被引入,它是MySQL对 ANSI SQL92 的一个扩展。
当你使用INSERT DELAYED
时,客户将马上准备好,并且当表不被任何其他的线程使用时,行将被插入。
另一个使用INSERT DELAYED
的主要好处是从很多客户插入被捆绑在一起并且写进一个块。这比做很多单独的插入要来的快。
注意,当前排队的行只是存储在内存中,直到他们被插入到表中。这意味着,如果你硬要杀死mysqld
(kill -9
)或如果mysqld
出人意料地死掉,没被写进磁盘的任何排队的行被丢失!
下列详细描述当你为INSERT
或REPLACE
使用DELAYED
选项时,发生什么。在这个描述中,“线程”是收到一个INSERT DELAYED
命令的线程并且“处理器”是处理所有对于一个特定表的INSERT DELAYED
语句。
- 当一个线程对一个表执行一个
DELAYED
语句时,如果不存在这样的处理程序,一个处理器线程被创建以处理对于该表的所有DELAYED
语句。 - 线程检查处理程序是否已经获得了一个
DELAYED
锁;如果没有,它告诉处理程序去获得。即使其他的线程有在表上的一个READ
或WRITE
锁,也能获得DELAYED
锁。然而,处理程序将等待所有ALTER TABLE
锁或FLUSH TABLES
以保证表结构是最新的。 - 线程执行
INSERT
语句,但不是将行写入表,它把最后一行的副本放进被处理器线程管理的一个队列。任何语法错误都能被线程发觉并报告给客户程序。 - 顾客不能报告结果行的重复次数或
AUTO_INCREMENT
值;它不能从服务器获得它们,因为INSERT
在插入操作完成前返回。如果你使用C API,同样原因,mysql_info()
函数不返回任何有意义的东西。 - 当行被插入到表中时,更新日志有处理器线程更新。在多行插入的情况下,当第一行被插入时,更新日志被更新。
- 在每写入
delayed_insert_limit
行后,处理器检查是否任何SELECT
语句仍然是未完成,如果这样,在继续之前允许执行这些语句。 - 当处理器在它的队列中没有更多行时,表被解锁。如果在
delayed_insert_timeout
秒内没有收到新的INSERT DELAYED
命令,处理器终止。 - 如果已经有多于
delayed_queue_size
行在一个特定的处理器队列中未解决,线程等待直到队列有空间。这有助于保证mysqld
服务器对延迟的内存队列不使用所有内存。 - 处理器线程将在
Command
列的MySQL进程表中显示delayed_insert
。如果你执行一个FLUSH TABLES
命令或以KILL thread_id
杀死它,它将被杀死,然而,它在退出前首先将所有排队的行存进表中。在这期间,这次它将不从其他线程接受任何新的INSERT
命令。如果你在它之后执行一个INSERT DELAYED
,将创建一个新的处理器线程。 - 注意,上述意味着,如果有一个
INSERT DELAYED
处理器已经运行,INSERT DELAYED
命令有比正常INSERT
更高的优先级!其他更新命令将必须等到INSERT DELAY
排队变空、杀死处理器线程(用KILL thread_id
)或执行FLUSH TABLES
。 - 下列状态变量提供了关于
INSERT DELAYED
命令的信息:Delayed_insert_threads
处理器线程数量 Delayed_writes
用 INSERT DELAYED
被写入的行的数量Not_flushed_delayed_rows
等待被写入的行数字 你能通过发出一个
SHOW STATUS
语句或通过执行一个mysqladmin extended-status
命令察看这些变量。
注意如果桌子不在使用,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
没指定,文件必须位于服务器上。(LOCAL
在MySQL3.22.6或以后版本中可用。)
为了安全原因,当读取位于服务器上的文本文件时,文件必须处于数据库目录或可被所有人读取。另外,为了对服务器上文件使用LOAD DATA INFILE
,在服务器主机上你必须有file的权限。见6.5 由MySQL提供的权限。
如果你指定关键词LOW_PRIORITY
,LOAD 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;
REPLACE
和IGNORE
关键词控制对现有的唯一键记录的重复的处理。如果你指定REPLACE
,新行将代替有相同的唯一键值的现有行。如果你指定IGNORE
,跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项,当找到重复键键时,出现一个错误,并且文本文件的余下部分被忽略时。
如果你使用LOCAL
关键词从一个本地文件装载数据,服务器没有办法在操作的当中停止文件的传输,因此缺省的行为好像IGNORE
被指定一样。
LOAD DATA INFILE
是SELECT ... INTO OUTFILE
的逆操作,见7.12 SELECT
句法。为了将一个数据库的数据写入一个文件,使用SELECT ... INTO OUTFILE
,为了将文件读回数据库,使用LOAD DATA INFILE
。两个命令的FIELDS
和LINES
子句的语法是相同的。两个子句是可选的,但是如果指定两个,FIELDS
必须在LINES
之前。
如果你指定一个FIELDS
子句,它的每一个子句(TERMINATED BY
, [OPTIONALLY] ENCLOSED BY
和ESCAPED 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 BY
和FIELDS ESCAPED BY
值必须是一个单个字符。FIELDS TERMINATED BY
和LINES 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"
如果你指定OPTIONALLY
,ENCLOSED BY
字符仅被用于包围CHAR
和VARCHAR
字段:
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 [OPTIONALLY] ENCLOSED BY
字符FIELDS TERMINATED BY
和LINES TERMINATED BY
值的第一个字符- ASCII
0
(实际上将后续转义字符写成 ASCII'0'
,而不是一个零值字节)
如果FIELDS ESCAPED BY
字符是空的,没有字符被转义。指定一个空转义字符可能不是一个好主意,特别是如果在你数据中的字段值包含刚才给出的表中的任何字符。
对于输入,如果FIELDS ESCAPED BY
字符不是空的,该字符的出现被剥去并且后续字符在字面上作为字段值的一个部分。例外是一个转义的“0”或“N”(即,\0
或\N
,如果转义字符是“\”)。这些序列被解释为ASCII 0
(一个零值字节)和NULL
。见下面关于NULL
处理的规则。
对于更多关于“\”- 转义句法的信息,见7.1 文字:怎样写字符串和数字。
在某些情况下,字段和行处理选项相互作用:
- 如果
LINES TERMINATED BY
是一个空字符串并且FIELDS TERMINATED BY
是非空的,行也用FIELDS TERMINATED BY
终止。 - 如果
FIELDS TERMINATED BY
和FIELDS ENCLOSED BY
值都是空的(''
),一个固定行(非限定的)格式被使用。用固定行格式,在字段之间不使用分隔符。相反,列值只用列的“显示”宽度被写入和读出。例如,如果列被声明为INT(7)
,列的值使用7个字符的字段被写入。对于输入,列值通过读取7个字符获得。固定行格式也影响NULL
值的处理;见下面。注意如果你正在使用一个多字节字符集,固定长度格式将不工作。
NULL
值的处理有多种,取决于你使用的FIELDS
和LINES
选项:
- 对于缺省
FIELDS
和LINES
值,对输出,NULL
被写成\N
,对输入,\N
被作为NULL
读入(假定ESCAPED BY
字符是“\”)。 - 如果
FIELDS ENCLOSED BY
不是空的,包含以文字词的NULL
作为它的值的字段作为一个NULL
值被读入(这不同于包围在FIELDS ENCLOSED BY
字符中的字NULL
,它作为字符串'NULL'
读入)。 - 如果
FIELDS ESCAPED BY
是空的,NULL
作为字NULL
被写入。 - 用固定行格式(它发生在
FIELDS TERMINATED BY
和FIELDS ENCLOSED BY
都是空的时候),NULL
作为一个空字符串被写入。注意,在写入文件时,这导致NULL
和空字符串在表中不能区分,因为他们都作为空字符串被写入。如果在读回文件时需要能区分这两者,你应该不使用固定行格式。
一些不被LOAD DATA INFILE
支持的情况:
- 固定长度的行(
FIELDS TERMINATED BY
和FIELDS ENCLOSED BY
都为空)和BLOB
或TEXT
列。 - 如果你指定一个分隔符与另一个相同,或是另一个的前缀,
LOAD DATA INFILE
不能正确地解释输入。例如,下列FIELDS
子句将导致问题:FIELDS TERMINATED BY '"' ENCLOSED BY '"'
- 如果
FIELDS ESCAPED BY
是空的,一个包含跟随FIELDS TERMINATED BY
值之后的FIELDS ENCLOSED BY
或LINES TERMINATED BY
的字段值将使得LOAD DATA INFILE
过早地终止读取一个字段或行。这是因为LOAD DATA INFILE
不能正确地决定字段或行值在哪儿结束。
下列例子装载所有persondata
表的行:
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
没有指定字段表,所以LOAD DATA INFILE
期望输入行对每个表列包含一个字段。使用缺省FIELDS
和LINES
值。
如果你希望仅仅装载一张表的某些列,指定一个字段表:
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
如果在输入文件中的字段顺序不同于表中列的顺序,你也必须指定一个字段表。否则,MySQL不能知道如何匹配输入字段和表中的列。
如果一个行有很少的字段,对于不存在输入字段的列被设置为缺省值。缺省值赋值在7.7 CREATE TABLE
句法中描述。
如果字段值缺省,空字段值有不同的解释:
- 对于字符串类型,列被设置为空字符串。
- 对于数字类型,列被设置为
0
。 - 对于日期和时间类型,列被设置为该类型的适当“零”值。见7.3.6 日期和时间类型。
如果列有一个NULL
,或(只对第一个TIMESTAMP
列)在指定一个字段表时,如果TIMESTAMP
列从字段表省掉,TIMESTAMP
列只被设置为当前的日期和时间。
如果输入行有太多的字段,多余的字段被忽略并且警告数字加1。
LOAD DATA INFILE
认为所有的输入是字符串,因此你不能像你能用INSERT
语句的ENUM
或SET
列的方式使用数字值。所有的ENUM
和SET
值必须作为字符串被指定!
如果你正在使用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
, reload
或flush-tables
命令来访问上述的每一个命令。
7.20 KILL
句法
KILL thread_id
每个对mysqld
的连接以一个单独的线程运行。你可以用看SHOW PROCESSLIST
命令察看哪个线程正在运行,并且用KILL thread_id
命令杀死一个线程。
如果你有process权限,你能看到并且杀死所有线程。否则,你只能看到并且杀死你自己的线程。
你也可以使用mysqladmin processlist
和mysqladmin 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 TABLES
或mysqlshow 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 FIELDS
是SHOW COLUMNS
一个同义词,SHOW KEYS
是SHOW INDEX
一个同义词。你也可以用mysqlshow db_name tbl_name
或mysqlshow -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 |
服务器工作了多少秒。 |
关于上面的一些注释:
- 如果
Opened_tables
太大,那么你的table_cache
变量可能太小。 - 如果
key_reads
太大,那么你的key_cache
可能太小。缓存命中率可以用key_reads
/key_read_requests
计算。 - 如果
Handler_read_rnd
太大,那么你很可能有大量的查询需要MySQL扫描整个表或你有没正确使用键值的联结(join)。
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 | +------------------------+--------------------------+
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_name
是DESCRIBE tbl_name
或SHOW COLUMNS FROM tbl_name
的一个同义词。
当你在一条SELECT
语句前放上关键词EXPLAIN
,MySQL解释它将如何处理SELECT
,提供有关表如何联结和以什么次序联结的信息。
借助于EXPLAIN
,你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT
。你也能知道优化器是否以一个最佳次序联结表。为了强制优化器对一个SELECT
语句使用一个特定联结次序,增加一个STRAIGHT_JOIN
子句。
对于非简单的联结,EXPLAIN
为用于SELECT
语句中的每个表返回一行信息。表以他们将被读入的顺序被列出。MySQL用一边扫描多次联结的方式解决所有联结,这意味着MySQL从第一个表中读一行,然后找到在第二个表中的一个匹配行,然后在第3个表中等等。当所有的表被处理完,它输出选择的列并且回溯表列表直到找到一个表有更多的匹配行,从该表读入下一行并继续处理下一个表。
从EXPLAIN
的输出包括下面列:
table
- 输出的行所引用的表。
type
- 联结类型。各种类型的信息在下面给出。
possible_keys
possible_keys
列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于表的次序。这意味着在possible_keys中的某些键实际上不能以生成的表次序使用。如果该列是空的,没有相关的索引。在这种情况下,你也许能通过检验WHERE
子句看是否它引用某些列或列不是适合索引来提高你的查询性能。如果是这样,创造一个适当的索引并且在用EXPLAIN
检查查询。见7.8ALTER 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
类型。它用在一个索引的所有部分被联结使用并且索引是UNIQUE
或PRIMARY KEY
。 ref
- 对于每个来自于先前的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联结只使用键的最左面前缀,或如果键不是
UNIQUE
或PRIMARY 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;
对于这个例子,假定:
- 被比较的列被声明如下:
表 列 列类型 tt
ActualPC
CHAR(10)
tt
AssignedPC
CHAR(10)
tt
ClientID
CHAR(10)
et
EMPLOYID
CHAR(15)
do
CUSTNMBR
CHAR(15)
- 表有显示在下面的索引:
表 索引 tt
ActualPC
tt
AssignedPC
tt
ClientID
et
EMPLOYID
(主键)do
CUSTNMBR
(主键) tt.ActualPC
值不是均匀分布的。
开始,在任何优化被施行前,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(还)不能高效地在列上使用索引。在本文中,VARCHAR
和CHAR
是相同的,除非他们声明为不同的长度。因为tt.ActualPC
被声明为CHAR(10)
并且et.EMPLOYID
被声明为CHAR(15)
,有一个长度失配。
为了修正在列长度上的不同,使用ALTER TABLE
将ActualPC
的长度从10个字符变为15个字符:
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
现在tt.ActualPC
和et.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.EMPLOYID
和tt.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 隐含的列说明变化。
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
锁,那么只有持锁的线程READ
或WRITE
表,其他线程被阻止。
每个线程等待(没有超时)直到它获得它请求的所有锁。
WRITE
锁通常比READ
锁有更高的优先级,以确保更改尽快被处理。这意味着,如果一个线程获得READ
锁,并且然后另外一个线程请求一个WRITE
锁, 随后的READ
锁请求将等待直到WRITE
线程得到了锁并且释放了它。当线程正在等待WRITE
锁时,你可以使用LOW_PRIORITY WRITE
允许其他线程获得READ
锁。如果你肯定终于有个时刻没有线程将有一个READ
锁,你应该只使用LOW_PRIORITY WRITE
。
当你使用LOCK TABLES
时,你必须锁定你将使用的所有表!如果你正在一个查询中多次使用一张表(用别名),你必须对每个别名得到一把锁!这条政策保证表锁定不会死锁。
注意你应该不锁定任何你正在用INSERT DELAYED
使用的表,这是因为在这种情况下,INSERT
被一个不同的线程执行。
通常,你不必锁定表,因为所有单个UPDATE
语句是原语;没有其他线程能防碍任何其它正在执行SQL语句的线程。当你想锁定表,有一些情况:
- 如果你将在一堆表上运行许多操作,锁定你将使用的表是较快的。当然缺点是,没有其他线程能更新一个
READ
锁定的表并且没有其他线程能读一个WRITE
-锁定的表。 - MySQL不支持事务环境,所以如果你想要保证在一个
SELECT
和一个UPDATE
之间没有其他线程到来,你必须使用LOCK TABLES
。下面显示的例子要求LOCK TABLES
以便安全地执行:mysql> LOCK TABLES trans READ, customer WRITE; mysql> select sum(value) from trans where customer_id= some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> UNLOCK TABLES;
没有LOCK TABLES
,另外一个线程可能有一个机会在执行SELECT
和UPDATE
语句之间往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_name
的DEFAULT
值恢复。注意设置CHARACTER SET
选项的语法不同于设置其他选项目的语法。 PASSWORD = PASSWORD('some password')
- 设置当前用户的口令。任何非匿名的用户能改变他自己的口令!
PASSWORD FOR user = PASSWORD('some password')
- 设置当前服务器主机上的一个特定用户的口令。只有具备存取
mysql
数据库的用户可以这样做。用户应该以user@hostname
格式给出,这里user
和hostname
完全与他们列在mysql.user
表条目的User
和Host
列一样。例如,如果你有一个条目其User
和Host
字段是'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
,所有INSERT
、UPDATE
、DELETE
和LOCK TABLE WRITE
语句等待,直到在受影响的表上没有未解决的SELECT
或LOCK 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 GRANT
和REVOKE
句法
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 ...]
GRANT
在MySQL 3.22.11或以后版本中实现。对于更早MySQL版本,GRANT
语句不做任何事情。
GRANT
和REVOKE
命令允许系统主管在4个权限级别上授权和撤回赋予MySQL用户的权利:
- 全局级别
- 全局权限作用于一个给定服务器上的所有数据库。这些权限存储在
mysql.user
表中。 - 数据库级别
- 数据库权限作用于一个给定数据库的所有表。这些权限存储在
mysql.db
和mysql.host
表中。 - 表级别
- 表权限作用于一个给定表的所有列。这些权限存储在
mysql.tables_priv
表中。 - 列级别
- 列权限作用于在一个给定表的单个列。这些权限存储在
mysql.columns_priv
表中。
对于GRANT
如何工作的例子,见6.11 为MySQL增加新的用户权限。
对于GRANT
和REVOKE
语句,priv_type
可以指定下列的任何一个:
ALL PRIVILEGES FILE RELOAD ALTER INDEX SELECT CREATE INSERT SHUTDOWN DELETE PROCESS UPDATE DROP REFERENCES USAGE
ALL
是ALL PRIVILEGES
的一个同义词,REFERENCES
还没被实现,USAGE
当前是“没有权限”的一个同义词。它能用在你想要创建一个没有权限用户的时候。
为了从一个用户撤回grant的权限,使用GRANT OPTION
的一个priv_type
值:
REVOKE GRANT OPTION ON ... FROM ...;
对于表,你能指定的唯一priv_type
值是SELECT
、INSERT
、UPDATE
、DELETE
、CREATE
、DROP
、GRANT
、INDEX
和ALTER
。
对于列,你能指定的唯一priv_type
值是(即,当你使用一个column_list
子句时)是SELECT
、INSERT
和UPDATE
。
你能通过使用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
。
简单形式的user
是user@"%"
的一个同义词。注意:如果你允许匿名用户连接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权限,用户能放弃insert、select和update。
你不应该将alter权限授予一个一般用户。如果你这样做,用户可以通过重命名表试图颠覆权限系统!
注意,如果你正在使用即使一个用户的表或列的权限,服务器要检查所有用户的表和列权限并且这将使MySQL慢下来一点。
当mysqld
启动时,所有的权限被读入存储器。数据库、表和列权限马上生效,而用户级权限在下一次用户连接时生效。你用GRANT
或REVOKE
对受权表执行的更改立即被服务器知晓。如果你手工修改授权表(使用INSERT
、UPDATE等等),你应该执行一个FLUSH PRIVILEGES
语句或运行mysqladmin flush-privileges
告诉服务器再次装载授权表。见6.9 权限变化何时生效。
ANSI SQL版本的GRANT
与MySQL版本之间的最大差别:
- ANSI SQL 没有全局或数据库级别权限,并且 ANSI SQL 不支持所有MySQL支持的权限。
- 当你在 ANSI SQL 抛弃一张表时,表的所有权限均被撤消。如果你在 ANSI SQL 撤销权限,所有基于该权限的授权也被也被撤消。在MySQL中,权限只能用明确的
REVOKE
命令或操作MySQL授权表抛弃。
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,...)
形式的列表创造一个多列索引。索引值有给定列的值串联而成。
对于CHAR
和VARCHAR
列,索引可以只用一个列的部分来创建,使用col_name(length)
句法。(在BLOB
和TEXT
列上需要长度)。下面显示的语句使用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 INDEX
从tbl_name
表抛弃名为index_name
的索引。DROP INDEX
在MySQL 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
客户分析/* ... */
注释的方式上有一些限制:
- 单引号和双引号字符被用来标志一个括起来的字符串的开始,即使在一篇注释内。如果引号在主室内没被第2个引号匹配,分析器不知道注释已经结束。如果你正在交互式运行
mysql
,你能告知有些混乱,因为提示符从mysql>
变为'>
或">
。 - 一个分号被用来指出当前的SQL语句结束并且跟随它的任何东西表示下一行的开始。
当你交互式运行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的方法。
AGGREGATE
是MySQL 3.23的一个新选项。一个AGGREGATE
函数功能就像一个原生MySQL GROUP
函数如SUM
或COUNT()
。
CREATE FUNCTION
在mysql.func
系统表中保存函数名、类型和共享库名。你必须对mysql
数据库有insert和delete权限以创建和抛弃函数。
所有活跃的函数在每次服务器启动时被重新装载,除非你使用--skip-grant-tables
选项启动mysqld
,在这种情况下,UDF初始化被跳过并且UDF是无法获得的。(一个活跃函数是一个已经用CREATE FUNCTION
装载并且没用DROP FUNCTION
删除的函数。)
关于编写用户可定义函数的指令,见14 为MySQL增加新函数。对于UDF的工作机制,函数必须用 C 或 C++ 编写,你的操作系统必须支持动态装载并且你必须动态编译了mysqld
(不是静态)。
7.31 MySQL对保留词很挑剔吗?
一个常见的问题源自于试图使用MySQL内置的数据类型或函数名同名的列来创建数据库表,例如TIMESTAMP
或GROUP
。你允许这样做(例如,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允许作为列/表名。这是因为这些名字的一些是很自然的名字并且很多人已经使用了他们。
ACTION
BIT
DATE
ENUM
NO
TEXT
TIME
TIMESTAMP