MySQL与尺度的兼容性[MySQL防范]

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语句..

SHOW语句.见7.21 SHOW句法(得到表、列等的信息).

字符串可以被"""或"'"包抄,而不只是"'".

利用"\"转义字符.

SET OPTION语句.见7.25 SET OPTION句法.

你不需求命名全部在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;

以ANSI情势运行MySQL

假如你用--ansi选项启动mysqld,MySQL的下列行为改变.

||是字符串并置而不是OR.

可在一个函数名字之间与"("有任何数目的空格.这也使全部的功效名字成为保存词.

"将是一个标识符引号字符(象MySQL `引号字符一样)而不是一个字符串引号字符.

REAL将是FLOAT一个同义词,不是DOUBLE一个同义词.

5.3 MySQL相比ANSI SQL92的差别

我们尝试使得MySQL遵循ANSI SQL尺度和ODBC SQL尺度,但是在一些情形下,MySQL做一些差别的事情:

--只是一个注释,假如背面跟一个白空字符.`--'作为一个注释的开始.

关于VARCHAR列,当值被存储时,拖后的空格被删除.见E MySQL已知的错误和计划缺限.

在一些情形下,CHAR列偷偷地被改变成VARCHAR列.安静的列指定改变.

当你删除一个表时,对表的权限不自动地撤废.你必须明确地发出一个REVOKE来撤废对一个表的权限.见7.26 GRANT和REVOKE句法.

MySQL贫乏的功效

下列功效在当前的MySQL版本是没有的.关于一张优先级表指出什么时刻新扩大可以加入MySQL, 你应当咨询在线MySQL TODO 表.这是本手册最新的TODO表版本.见F 我们想要在将来加入到MySQL的事情列表(TODO).

子挑选

在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().

SELECT INTO TABLE

MySQL还不支持Oracle SQL的扩大:SELECT ... INTO TABLE ....,相反MySQL支持ANSI SQL句法INSERT INTO ... SELECT ...,基本上他们是一样的.

别的,你可以利用SELECT INTO OUTFILE...或CREATE TABLE ... SELECT办理你的问题.

[1] [2] 下一页  

事件处理

不支持事件处理.MySQL将在短时间内支持原子(atomic)操作,它象没有回卷的事件.用原子操作,你能履行一组INSERT/SELECT/whatever 号令并且保证没有其他线程参与.在本文中,你普通不会需求回卷.目前,你可通过利用LOCK TABLES和UNLOCK TABLES号令禁止其他线程的干扰.见7.24 LOCK TABLES/UNLOCK TABLES句法.

存储历程和触发器

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

一个触发器是当一个分外的事件发生时,被调用的一个存储历程.比方,你可以安装一个存储历程,它在每次从一个交易表删除一条记录时触发,并且当它全部交易被删除时,自动地从一个客户表中删除呼应的客户.

筹划改正的语言将能处理存储历程,但是没有触发器.触发器普通使每件事情变慢,即便对他们不需求的查询.

为了俩解什么时刻MySQL大概得到存储历程,见F 我们想在将来加入到MySQL的事情列表(TODO).

外键

注意,在SQL中外键不用于联合表,而主要用于查抄参考完好性(RI).假如你想要得到用一个SELECT语句从多个表得到后果, 你通过联合表做!

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

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

在MySQL中,你可以办理ON DELETE ...没被实现的问题,,在你从一个用外键的表删除记录时,通过为一个利用程序增添得当的DELETE语句便可.实际上,这很快(在一些情形下更快)并且比利用外键更比便于移植.

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

不利用外键的来由

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

外键使生活更复杂,因为外键的定义必须存储在一个数据库中并且实现他们将破坏利用能被移动、拷贝和删除文件的全部"好办法".

速度影响对INSERT和UPDATE语句是可怕的,并且在这种情形下几近全部的FOREIGN KEY查抄都是无用的,因为不管怎样你普通以精确的次序在精确的表中插入记录.

当更新一张表时,也有在很多表上保存锁的需求,因为副作用可以串通通过全部数据库.首先从一张表中删除记录并且随后从其他表中删除他们,这更快.

你再也不可以通过做一个全面的表删除并随后恢复全部的记录的办法来恢复一张表(重新根源或从一个备份).

假如你有外键,你不能倾倒和恢复表,除非你以一个非常特定的做这些.

很简单做一个"答应的"的循环定义使得不大概用一个单个create语句重建每一个表,就算定义可行又可用.

FOREIGN KEY唯一好的方面是它给ODBC和一些其他客户程序查抄一张表若何被衔接的本领,并且利用它们显示出衔接图表并帮忙构造利用.

MySQL不久将存储FOREIGN KEY定义以便一个客户能询问并收到本来的衔接若何举行的一个答案.当前的".frm 文件格局没有它应有的地位.

视图

MySQL不支持视图,但是它在TODO上.

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

有些其他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

MySQL 遵守什么尺度?

Entry level SQL92.ODBC level 0-2.

怎样处理没有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,你可以利用下列战略:

利用LOCK TABLES ...锁住全部你想要存取的数据库表.

测试条件

假如一切无误,更新.

利用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;