MySQL中文参考手册

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

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

5 MySQL与标准的兼容性？

5.1   MySQL对ANSI SQL92扩充

MySQL包含了一些可能在其他SQL数据库找不到的扩充。要注意如果你使用他们，你的代码将不与其他SQL服务器兼容。在一些情况下，你可以编写包括MySQL扩展的代码，但是仍然是可移植的，通过使用/*! ... */形式的注释。在这种情况下，MySQL将进行词法分析并且执行在注释内的代码，好像它是任何其它MySQL语句，但是其他SQL服务器将忽略扩展。例如：

SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ... 

如果你在'!'后增加一个版本数字，该语法将仅在MySQL版本是等于或比使用的版本数字新时才执行：

CREATE /*!32302 TEMPORARY */ TABLE (a int);

上面的意思是如果你有3.23.02或更新，那么MySQL将使用TEMPORARY关键词。

MySQL扩展被列在下面：

• 字段类型MEDIUMINT、SET、ENUM和不同的BLOB和TEXT类型。
• 字段属性AUTO_INCREMENT、BINARY、UNSIGNED和ZEROFILL。
• 缺省地，所有的字符串比较是忽略大小写的，由当前的字符集决定了(缺省为ISO-8859-1 Latin1)排序顺序。如果你不喜欢这样，你应该用BINARY属性或使用BINARY强制符声明列，它导致根据MySQL服务器主机的ASCII顺序进行排序。
• MySQL将每个数据库映射一个MySQL数据目录下面的目录，将数据库表映射到数据库目录下的数据库文件名。这有2个含意：
  • 在区分大小写文件名的操作系统(象大多数 Unix 系统一样)上的MySQL中数据库名字和表名是区分大小写的。如果你有困难记得表名，接受一个一致的约定，例如总是用小写名字创建数据库和表。
  • 数据库、表、索引、列或别名可以以数字开始(但是不能仅由数字组成)。
  • 你可以使用标准的系统命令备份、重命名、移动、删除和拷贝表。例如，重命名一个表，重命名“.MYD”、“.MYI”和“.frm”文件为相应的表。
• 在SQL语句中，你可以用db_name.tbl_name语法访问不同数据库中的表。一些SQL服务器提供同样的功能但是称它们为这User space（用户空间）。MySQL不支持类似在create table ralph.my_table...IN my_tablespace中的表空间。
• LIKE在数字列上被允许。
• 在一SELECT语句里面使用INTO OUTFILE和STRAIGHT_JOIN。见7.12 SELECT句法.
• 在一个SELECT语句中SQL_SMALL_RESULT选项。
• EXPLAIN SELECT得到如何联结表的描述。
• 在一个CREATE TABLE语句里面使用索引、在字段前缀上的索引和使用INDEX或KEY。见7.7 CREATE TABLE 句法。
• CREATE TABLE使用TEMPORARY或IF NOT EXISTS。
• 使用COUNT(DISTINCT list)，这里“list”超过一个元素。
• 在一个ALTER TABLE语句里面使用CHANGE col_name、DROP col_name或DROP INDEX。见7.8 ALTER TABLE句法。
• 在一个ALTER TABLE里面语句使用IGNORE。
• 在一个ALTER TABLE语句中使用多重ADD、ALTER、DROP或CHANGE子句。
• 使用带关键词IF EXISTS的DROP TABLE。
• 你能用单个DROP TABLE语句抛弃多个表。
• DELETE语句的LIMIT子句。
• INSERT和REPLACE语句的DELAYED子句。
• INSERT, REPLACE, DELETE和UPDATE语句的LOW_PRIORITY子句。
• 使用LOAD DATA INFILE。在多数情况下，这句法与Oracle的LOAD DATA INFILE兼容。见7.16 LOAD DATA INFILE 句法。
• OPTIMIZE TABLE语句。见7.9 OPTIMIZE TABLE句法。
• SHOW语句。见7.21 SHOW句法(得到表、列等的信息)。
• 字符串可以被“"”或“'”包围，而不只是“'”。
• 使用“\”转义字符。
• SET OPTION语句。见7.25 SET OPTION句法。
• 你不需要命名所有在GROUP BY部分的被选择的列。这为一些很特定的情况给出更好的性能，而不是一般的查询。见7.4.13 用于GROUP BY子句的函数。
• 为了方便来自于SQL环境其他为用户，MySQL对许多函数支持别名。例如，所有的字符串功能都支持ANSI SQL句法和 ODBC句法。
• MySQL理解||和&&意味着逻辑的OR和AND，就像在C程序语言中。在MySQL中，||和OR是同义词，&&和AND是同义词。正因为这个好的句法，MySQL对字符串并置的不支持ANSI SQL ||操作符；相反使用CONCAT()，因为CONCAT()接受任何数量的参数，很容易把||操作符使用变换到MySQL。
• CREATE DATABASE或DROP DATABASE。见7.5 CREATE DATABASE句法。
• %操作符是MOD()一个同义词，即，N % M等价于MOD(N,M)。%支持C程序员并与PostgreSQL兼容。
• =, <>, <=,<, >=,>, <<, >>, <=>, AND, OR或LIKE操作符可以放在SELECT语句的FROM左边用于比较列。例如：

  mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
  

• LAST_INSERT_ID()函数。见20.4.29 mysql_insert_id()。
• 扩展的正则表达式操作符REGEXP和NOT REGEXP。
• CONCAT()或CHAR()有一个参数或超过2个参数。（在MySQL中，这些函数可取任何数量的参数。）
• BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), md5(), ENCODE(), DECODE(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(),或WEEKDAY()函数。
• 使用TRIM()整修子串。ANSI SQL 只支持单个字符的删除。
• GROUP BY函数STD(), BIT_OR()和BIT_AND()。
• 使用REPLACE而不是DELETE+INSERT。见7.15 REPLACE句法。
• FLUSH flush_option语句。
• 在一个语句用:=设置变量的可能性：

  SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table;
  SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
  

5.2 以ANSI模式运行MySQL

如果你用--ansi选项启动mysqld，MySQL的下列行为改变。

• ||是字符串并置而不是OR。
• 可在一个函数名字之间与“(”有任何数量的空格。这也使所有的功能名字成为保留词。
• "将是一个标识符引号字符(象MySQL `引号字符一样)而不是一个字符串引号字符。
• REAL将是FLOAT一个同义词，不是DOUBLE一个同义词。

5.3 MySQL相比ANSI SQL92的差别

我们尝试使得MySQL遵照ANSI SQL标准和ODBC SQL标准，但是在一些情况下，MySQL做一些不同的事情：

• --只是一个注释，如果后面跟一个白空字符。见5.4.7 `--'作为一个注释的开始。
• 对于VARCHAR列，当值被存储时，拖后的空格被删除。见E MySQL已知的错误和设计缺限。
• 在一些情况下，CHAR列偷偷地被改变为VARCHAR列。见7.7.1 平静的列指定变化。
• 当你删除一个表时，对表的权限不自动地废除。你必须明确地发出一个REVOKE来废除对一个表的权限。见7.26 GRANT和REVOKE句法。

5.4 MySQL缺乏的功能

下列功能在当前的MySQL版本是没有的。对于一张优先级表指出何时新扩展可以加入MySQL, 你应该咨询在线MySQL TODO 表。这是本手册最新的TODO表版本。见F 我们想要在未来加入到MySQL的事情列表(TODO)。

5.4.1 子选择

在MySQL中下列语句还不能工作：

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);

然而，在很多情况下，你可以重写查询，而不用子选择：

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL

对于更复杂的子查询，通常你可以创建临时的表保存子查询。然而在一些情况下，这种选择将行不通。最经常遇到的情形是DELETE语句，对于它标准SQL不支持联结(join)(除了在子选择)。对于这种情况，有2个可用选择，直到子选择被MySQL支持。

第一个选择是使用一种过程化的程序语言(例如Perl或PHP)来提交一个SELECT查询获得要被删除记录主键，并然后使用这些值构造DELETE语句(DELETE FROM ... WHERE ... IN (key1, key2, ...))。

第二个选择是使用交互式SQL自动构造一套DELETE语句，使用MySQL扩展CONCAT()（代替标准||操作符)。例如：

SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';')
  FROM tab1, tab2
 WHERE tab1.col1 = tab2.col2;

你可以把这个查询放在一个脚本文件并且从它重定向输入到mysql命令行解释器，将其输出作为管道返回给解释器的第2个实例：

prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb

MySQL仅支持INSERT ... SELECT ...和REPLACE ... SELECT ...，独立的子选择将可能在3.24.0得到，然而，在其他环境下，你现在可以使用函数IN()。

5.4.2 SELECT INTO TABLE

MySQL还不支持Oracle SQL的扩展：SELECT ... INTO TABLE ....，相反MySQL支持ANSI SQL句法INSERT INTO ... SELECT ...，基本上他们是一样的。

另外，你可使用SELECT INTO OUTFILE...或CREATE TABLE ... SELECT解决你的问题。

5.4.3 事务处理

不支持事务处理。MySQL将在短时间内支持原子(atomic)操作，它象没有回卷的事务。用原子操作，你能执行一组INSERT/SELECT/whatever 命令并且保证没有其他线程介入。在本文中，你通常不会需要回卷。目前，你可通过使用LOCK TABLES和UNLOCK TABLES命令阻止其他线程的干扰。见7.24 LOCK TABLES/UNLOCK TABLES句法。

5.4.4 存储过程和触发器

一个存储过程是能在服务器中编译并存储的一套SQL命令。一旦这样做了，顾客不需要一直重新发出全部查询，而可以参考存储过程。因为查询仅需一次词法分析并且较少的信息需要在服务器和客户之间传送，因此这提供了更好的性能。你与可以通过拥有在服务器中的函数库提升概念上的层次。

一个触发器是当一个特别的事件发生时，被调用的一个存储过程。例如，你可以安装一个存储过程，它在每次从一个交易表删除一条记录时触发，并且当它所有交易被删除时，自动地从一个客户表中删除相应的客户。

计划修改的语言将能处理存储过程，但是没有触发器。触发器通常使每件事情变慢，即使对他们不需要的查询。

为了俩解什么时候MySQL可能得到存储过程，见F 我们想在未来加入到MySQL的事情列表(TODO)。

5.4.5 外键

注意，在SQL中外键不用于联结表，而主要用于检查参考完整性(RI)。如果你想要得到用一个SELECT语句从多个表得到结果， 你通过联结表做！

SELECT * from table1,table2 where table1.id = table2.id; 

见7.13 JOIN句法。见8.3.5 使用外键。

在MySQL里存在FOREIGN KEY句法仅仅为了与其他SQL供应商的CREATE TABLE命令相兼容；它不做任何事情。没有ON DELETE ...的FOREIGN KEY句法主要用于文档目的。一些ODBC应用程序可以使用它自动生成WHERE子句，但是这通常很容易的覆盖。 FOREIGN KEY有时用作一个约束检查，但是如果行以正确的顺序被插入表，该检查实际上是不必要的。MySQL仅仅支持这些子句(不考虑是否他们工作!)，因为一些应用程序要求他们存在。

在MySQL中，你可以解决ON DELETE ...没被实现的问题，，在你从一个用外键的表删除记录时，通过为一个应用程序增加适当的DELETE语句即可。实际上，这很快(在一些情况下更快)并且比使用外键更比便于移植。

在不久的将来我们将扩充FOREIGN KEY实现，以便至少信息将在表说明文件中保存并且可以由mysqldump和ODBC检索。

5.4.5.1 不使用外键的理由

有很多与FOREIGN KEY有关的问题我们不知道从哪儿开始：

• 外键使生活更复杂，因为外键的定义必须存储在一个数据库中并且实现他们将破坏使用能被移动、拷贝和删除文件的全部“好方法”。
• 速度影响对INSERT和UPDATE语句是可怕的，并且在这种情况下几乎所有的FOREIGN KEY检查都是无用的，因为不管怎样你通常以正确的顺序在正确的表中插入记录。
• 当更新一张表时，也有在许多表上保存锁的需求，因为副作用可以串联通过全部数据库。首先从一张表中删除记录并且随后从其他表中删除他们，这更快。
• 你再也不可以通过做一个全面的表删除并随后恢复所有的记录的方法来恢复一张表(从新来源或从一个备份)。
• 如果你有外键，你不能倾倒和恢复表，除非你以一个非常特定的做这些。
• 很容易做一个“允许的”的循环定义使得不可能用一个单个create语句重建每一个表，就算定义可行又可用。

FOREIGN KEY唯一好的方面是它给ODBC和一些其他客户程序检查一张表如何被连接的能力，并且使用它们显示出连接图表并帮助构造应用。

MySQL不久将存储FOREIGN KEY定义以便一个客户能询问并收到原来的连接如何进行的一个答案。当前的“.frm 文件格式没有它应有的地位。

5.4.6 视图

MySQL不支持视图，但是它在TODO上。

5.4.7 '--'作为一个注释的开始

有些其他SQL数据库使用'--'开始注释。MySQL有“#”作为开始数注释的字符，即使mysql命令行工具删除以'--'开始的所有行。你也可以在MySQL中使用C注释风格/* this is a comment */。见7.29 注释句法。

MySQL3.23.3和以上版本支持'--'注释风格，只要注释跟在一个空格之后。这是因为这种退化的注释风格已经引起用像下列代码那样的自动生成的SQL查询的许多问题，这里我们自动地为!payment!插入支付值：

UPDATE tbl_name SET credit=credit-!payment! 

你想出当payment的值是负的时将发生什么吗？

因为1--1在SQL中是合法的，我们认为'--'开始注释是可怕的。

然而在MySQL 3.23中，你可使用：1-- This is a comment

如果你正在运行一个比3.23早的MySQL的版本，下列的讨论才涉及你：

如果你在一个文本文件中有一个SQL程序，它包含'--'注释，你应该使用：

shell> replace " --" " #" < text-file-with-funny-comments.sql \
         | mysql database

而不是通常： 

shell> mysql database < text-file-with-funny-comments.sql

你也可以“现场”编辑命令文件将'--'注释改为'#'注释：

shell> replace " --" " #" -- text-file-with-funny-comments.sql

用这个命令改回他们：

shell> replace " #" " --" -- text-file-with-funny-comments.sql

5.5 MySQL 遵循什么标准？

Entry level SQL92。ODBC level 0-2。

5.6 怎样处理没有COMMIT/ROLLBACK

MySQL不支持COMMIT-ROLLBACK。问题是有效地处理COMMIT-ROLLBACK将需要完全不同于MySQL今天使用的表布局。MySQL也将需要额外的线程在表上做自动清除工作，而且磁盘用量将更高。这将使MySQL比现今慢上大约2-4倍。MySQL比几乎所有其他SQL数据库都快(一般至少快2-3倍)。原因之一就是缺少COMMIT-ROLLBACK。

目前，我们是更多地实现SQL服务器语言(象存储过程)，有了它，你将确实很少需要COMMIT-ROLLBACK，这也将得到更好的性能。

通常需要事务的循环可以借助LOCK TABLES进行编码，并且当你能即时地更新记录时，你不需要光标(cursor)。

我们在TODO上有事务和光标，然而并非相当优先。如果我们实现这些，将作为CREATE TABLE的选项，那意味着COMMIT-ROLLBACK将仅工作在那些表上，以便速度损失仅仅强加在那些表上。

我们在TcX有一个更大的需求，一个比100%通用数据库的真正快速的数据库。无论何时我们发现一个方法来实现这些特征而没有任何速度损失，我们将可能做它。暂时，有许多更重要的事情要做。检查TODO，看我们此时如何将事情优先排列。（有的较高级别支持的客户可以改变它，因此事情是可以重新优先化的。）

当前的问题实际上是ROLLBACK，没有ROLLBACK，你能用LOCK TABLES做任何COMMIT动作。为了支持ROLLBACK，MySQL将必须被改变以存储所有的旧记录，如果发出ROLLBACK，它们被更新的并且将任何东西恢复到起点。对于简单的情形，这不是难做的 (当前isamlog可以用于此目的)，但是为ALTER/DROP/CREATE TABLE实现ROLLBACK将是更困难的。

避免使用ROLLBACK，你可以使用下列策略：

1. 使用LOCK TABLES ...锁住所有你想要存取的数据库表。
2. 测试条件。
3. 如果一切无误，更新。
4. 使用UNLOCK TABLES释放你的锁。

这通常比使用可能带ROLLBACK的交易是一个更快的方法，尽管不总是这样。这个解决方案不能处理的唯一状况是当某人在更新当中杀死线程时。在这种情况下，所有的锁将被释放，但是一些更改不能被执行。

你也可使函数以单个操作更新记录。你能通过使用下列技术得到一个很有效率的应用程序：

• 相对他们的当前的值修改字段
• 仅仅更新那些实际上更改的字段

例如，当我们正在更新一些客户信息时，我们仅仅更新那些改变了的客户数据并只测试没有任何数据的改变，或数据取决于改变的数据，与原来的行相比变化了。对于改变了的数据的测试用WHERE子句在UPDATE语句中完成。如果记录没被更新，我们给客户一条消息：“你改变了的一些数据已被其他用户改变了”，然后我们在一个窗口中显示新行对照旧行，因此用户能决定他该使用哪个版本的客户记录。

这给了我们类似于“列锁定”的东西，但是实际上甚至更好，因为我们仅仅更新某些列，使用相对于他们的当前值的值。这意味着典型的UPDATE语句看上去象这些一样东西：

UPDATE tablename SET pay_back=pay_back+'relative change';

UPDATE customer
  SET
    customer_date='current_date',
    address='new address',
    phone='new phone',
    money_he_owes_us=money_he_owes_us+'new_money'
  WHERE
    customer_id=id AND address='old address' AND phone='old phone';

正如你能看到的，这是很有效的并且就算其他客户已经改变了pay_back或money_he_owes_us列的也能工作。

在许多情况下，为管理一些表格的唯一标识符目的，用户已经想要ROLLBACK或LOCK TABLES。这可用一个AUTO_INCREMENT列和一个SQL函数LAST_INSERT_ID()或C API函数mysql_insert_id()更高效地处理。见20.4.29 mysql_insert_id()。

在TcX，我们从来没有任何对行级锁定的需求，因为我们总是能通过编码解决它。一些情况下需要确实行锁定，但是他们是很少见的。如果你想要行级锁定，你可以在表中使用标志列并且这样做：

UPDATE tbl_name SET row_flag=1 WHERE id=ID; 

如果行被找到发现并且row_flag在原来的行已经不是1，对受影响的行数MySQL返回1。

你可以想到它，因为MySQL把上面的查询变为：

UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;

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