MYSQL编程：MySQL中文参考手册6(MySQL与尺度的兼容...

能够以较低的成本向客户提供IT所有权，当节约成本成为客户最高优先级时，解决方案提供商可以向更多的客户同时提供服务。虽然有许多来自RDBMS固有的局限性。mysql|尺度|参考|参考手册|中文
MySQL中文参考手册6(MySQL与尺度的兼容性)
转载译者：晏子

〖前往〗〖转发〗


q译者：晏子(clyan@sohu.com)主页：http://linuxdb.yeah.net

5MySQL与尺度的兼容性？
5.1MySQL对ANSISQL92扩大
MySQL包括了一些大概在其他SQL数据库找不到的扩大。要注重假如你利用他们，你的代码将不与其他SQL服务器兼容。在一些情形下，你能够编写包含MySQL扩大的代码，可是仍旧是可移植的，经由过程利用/*!...*/情势的正文。在这类情形下，MySQL将举行词法剖析而且实行在正文内的代码，仿佛它是任何别的MySQL语句，可是其他SQL服务器将疏忽扩大。比方：
SELECT/*!STRAIGHT_JOIN*/col_nameFROMtable1,table2WHERE...
假如你在!后增添一个版本数字，该语法将仅在MySQL版本是即是或比利用的版本数字新时才实行：

CREATE/*!32302TEMPORARY*/TABLE(aint);

下面的意义是假如你有3.23.02或更新，那末MySQL将利用TEMPORARY关头词。

MySQL扩大被列鄙人面：
字段范例MEDIUMINT、SET、ENUM和分歧的BLOB和TEXT范例。

字段属性AUTO_INCREMENT、BINARY、UNSIGNED和ZEROFILL。

缺省地，一切的字符串对照是疏忽巨细写的，由以后的字符集决意了(缺省为ISO-8859-1Latin1)排序按次。假如你不喜好如许，你应当用BINARY属性或利用BINARY强迫符声明列，它招致依据MySQL服务器主机的ASCII按次举行排序。

MySQL将每一个数据库映照一个MySQL数据目次上面的目次，将数据库表映照到数据库目次下的数据库文件名。这有2个含义：

在辨别巨细写文件名的操纵体系(象年夜多半Unix体系一样)上的MySQL中数据库名字和表名是辨别巨细写的。假如你有坚苦记得表名，承受一个分歧的商定，比方老是用小写名字创立数据库和表。
数据库、表、索引、列或别号能够以数字入手下手(可是不克不及仅由数字构成)。

你可使用尺度的体系命令备份、重定名、挪动、删除和拷贝表。比方，重定名一个表，重定名“.MYD”、“.MYI”和“.frm”文件为响应的表

在SQL语句中，你能够用db_name.tbl_name语法会见分歧数据库中的表。一些SQL服务器供应一样的功效可是称它们为这Userspace（用户空间）。MySQL不撑持相似在createtableralph.my_table...INmy_tablespace中的表空间。
LIKE在数字列上被同意。
在一SELECT语句内里利用INTOOUTFILE和STRAIGHT_JOIN。见7.12SELECT句法.
在一个SELECT语句中SQL_SMALL_RESULT选项。
EXPLAINSELECT失掉怎样联合表的形貌。
在一个CREATETABLE语句内里利用索引、在字段前缀上的索引和利用INDEX或KEY。见7.7CREATETABLE句法。
CREATETABLE利用TEMPORARY或IFNOTEXISTS。
利用COUNT(DISTINCTlist)，这里“list”凌驾一个元素。
在一个ALTERTABLE语句内里利用CHANGEcol_name、DROPcol_name或DROPINDEX。见7.8ALTERTABLE句法。
在一个ALTERTABLE内里语句利用IGNORE。
在一个ALTERTABLE语句中利用多重ADD、ALTER、DROP或CHANGE子句。
利用带关头词IFEXISTS的DROPTABLE。
你能用单个DROPTABLE语句丢弃多个表。
DELETE语句的LIMIT子句。
INSERT和REPLACE语句的DELAYED子句。
INSERT,REPLACE,DELETE和UPDATE语句的LOW_PRIORITY子句。
利用LOADDATAINFILE。在多半情形下，这句法与Oracle的LOADDATAINFILE兼容。见7.16LOADDATAINFILE句法。
OPTIMIZETABLE语句。见7.9OPTIMIZETABLE句法。
SHOW语句。见7.21SHOW句法(失掉表、列等的信息)。
字符串能够被“"”或“”包抄，而不但是“”。
利用“”本义字符。
SETOPTION语句。见7.25SETOPTION句法。
你不必要定名一切在GROUPBY部分的被选择的列。这为一些很特定的情形给出更好的功能，而不是一样平常的查询。见7.4.13用于GROUPBY子句的函数。
为了便利来自于SQL情况其他为用户，MySQL对很多函数撑持别号。比方，一切的字符串功效都撑持ANSISQL句法和ODBC句法。
MySQL了解||和&&意味着逻辑的OR和AND，就像在C程序言语中。在MySQL中，||和OR是同义词&&和AND是同义词。正由于这个好的句法，MySQL对字符串并置的不撑持ANSISQL||操纵符；相反利用CONCAT()，由于CONCAT()承受任何数目的参数，很简单把||操纵符利用变更到MySQL。
CREATEDATABASE或DROPDATABASE。见7.5CREATEDATABASE句法。

%操纵符是MOD()一个同义词，即，N%M等价于MOD(N,M)。%撑持C程序员并与PostgreSQL兼容。

=,,<=,<,>=,>,<<,>>,<=>,AND,OR或LIKE操纵符能够放在SELECT语句的FROM右边用于对照列。比方：
mysql>SELECTcol1=1ANDcol2=2FROMtbl_name;

LAST_INSERT_ID()函数。见20.4.29mysql_insert_id()。
扩大的正则表达式操纵符REGEXP和NOTREGEXP。
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()整修子串。ANSISQL只撑持单个字符的删除。
GROUPBY函数STD(),BIT_OR()和BIT_AND()。
利用REPLACE而不是DELETE+INSERT。见7.15REPLACE句法。
FLUSHflush_option语句。
在一个语句用:=设置变量的大概性：
SELECT@a:=SUM(total),@b=COUNT(*),@a/@bASavgFROMtest_table;
SELECT@t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;



5.2以ANSI形式运转MySQL
假如你用--ansi选项启动mysqld，MySQL的以下举动改动。

||是字符串并置而不是OR。
可在一个函数名字之间与“(”有任何数目的空格。这也使一切的功效名字成为保存词。

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

REAL将是FLOAT一个同义词，不是DOUBLE一个同义词。



5.3MySQL比拟ANSISQL92的不同
我们实验使得MySQL依照ANSISQL尺度和ODBCSQL尺度，可是在一些情形下，MySQL做一些分歧的事变：

--只是一个正文，假如前面跟一个白空字符。见5.4.7`--作为一个正文的入手下手。

关于VARCHAR列，当值被存储时，拖后的空格被删除。见EMySQL已知的毛病和计划缺限。
在一些情形下，CHAR列偷偷地被改动为VARCHAR列。见7.7.1僻静的列指定变更。
当你删除一个表时，对表的权限不主动地取销。你必需明白地收回一个REVOKE来取销对一个表的权限。见7.26GRANT和REVOKE句法。



5.4MySQL缺少的功效
以下功效在以后的MySQL版本是没有的。关于一张优先级表指出什么时候新扩大能够到场MySQL,你应当征询在线MySQLTODO表。这是本手册最新的TODO表版本。见F我们想要在将来到场到MySQL的事变列表(TODO)。

5.4.1子选择
在MySQL中以下语句还不克不及事情：

SELECT*FROMtable1WHEREidIN(SELECTidFROMtable2);
SELECT*FROMtable1WHEREidNOTIN(SELECTidFROMtable2);

但是，在良多情形下，你能够重写查询，而不必子选择：

SELECTtable1.*FROMtable1,table2WHEREtable1.id=table2.id;
SELECTtable1.*FROMtable1LEFTJOINtable2ONtable1.id=table2.idwheretable2.idISNULL

关于更庞大的子查询，一般你能够创立一时的表保留子查询。但是在一些情形下，这类选择将行欠亨。最常常碰到的情况是DELETE语句，关于它尺度SQL不撑持联合(join)(除在子选择)。关于这类情形，有2个可用选择，直到子选择被MySQL撑持。

第一个选择是利用一种历程化的程序言语(比方Perl或PHP)来提交一个SELECT查询取得要被删除纪录主键，并然后利用这些值机关DELETE语句(DELETEFROM...WHERE...IN(key1,key2,...))。

第二个选择是利用交互式SQL主动机关一套DELETE语句，利用MySQL扩大CONCAT()（取代尺度||操纵符)。比方：

SELECTCONCAT(DELETEFROMtab1WHEREpkid=,tab1.pkid,;)
FROMtab1,tab2
WHEREtab1.col1=tab2.col2;

你能够把这个查询放在一个剧本文件而且从它重定向输出到mysql命令行注释器，将其输入作为管道前往给注释器的第2个实例：

prompt>mysql--skip-column-namesmydb<myscript.sql|mysqlmydb

MySQL仅撑持INSERT...SELECT...和REPLACE...SELECT...，自力的子选择将大概在3.24.0失掉，但是，在其他情况下，你如今可使用函数IN()。

5.4.2SELECTINTOTABLE
MySQL还不撑持OracleSQL的扩大：SELECT...INTOTABLE....，相反MySQL撑持ANSISQL句法INSERTINTO...SELECT...，基础上他们是一样的。

别的，你可以使用SELECTINTOOUTFILE...或CREATETABLE...SELECT办理你的成绩。

5.4.3事件处置
不撑持事件处置。MySQL将在短工夫内撑持原子(atomic)操纵，它象没有回卷的事件。用原子操纵，你能实行一组INSERT/SELECT/whatever命令而且包管没有其他线程参与。在本文中，你一般不会必要回卷。今朝，你可经由过程利用LOCKTABLES和UNLOCKTABLES命令制止其他线程的搅扰。见7.24LOCKTABLES/UNLOCKTABLES句法。

5.4.4存储历程和触发器
一个存储历程是能在服务器中编译并存储的一套SQL命令。一旦如许做了，主顾不必要一向从头收回全体查询，而能够参考存储历程。由于查询仅需一次词法剖析而且较少的信息必要在服务器和客户之间传送，因而这供应了更好的功能。你与能够经由过程具有在服务器中的函数库提拔观点上的条理。

一个触发器是当一个出格的事务产生时，被挪用的一个存储历程。比方，你能够安装一个存储历程，它在每次从一个买卖表删除一笔记录时触发，而且当它一切买卖被删除时，主动地从一个客户表中删除响应的客户。

企图修正的言语将能处置存储历程，可是没有触发器。触发器一般使每件事变变慢，即便对他们不必要的查询。

为了俩解甚么时分MySQL大概失掉存储历程，见F我们想在将来到场到MySQL的事变列表(TODO)。

5.4.5外键
注重，在SQL中外键不必于联合表，而次要用于反省参考完全性(RI)。假如你想要失掉用一个SELECT语句从多个表失掉了局，你经由过程联合表做！

SELECT*fromtable1,table2wheretable1.id=table2.id;

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

在MySQL里存在FOREIGNKEY句法仅仅为了与其他SQL供给商的CREATETABLE命令相兼容；它不做任何事变。没有ONDELETE...的FOREIGNKEY句法次要用于文档目标。一些ODBC使用程序可使用它主动天生WHERE子句，可是这一般很简单的掩盖。FOREIGNKEY偶然用作一个束缚反省，可是假如行以准确的按次被拔出表，该反省实践上是不用要的。MySQL仅仅撑持这些子句(不思索是不是他们事情!)，由于一些使用程序请求他们存在。

在MySQL中，你能够办理ONDELETE...没被完成的成绩，，在你从一个用外键的表删除纪录时，经由过程为一个使用程序增添得当的DELETE语句便可。实践上，这很快(在一些情形下更快)而且比利用外键更比便于移植。

在不久的未来我们将扩大FOREIGNKEY完成，以便最少信息将在表申明文件中保留而且能够由mysqldump和ODBC检索。

5.4.5.1不利用外键的来由
有良多与FOREIGNKEY有关的成绩我们不晓得从哪儿入手下手：

外键使生存更庞大，由于外键的界说必需存储在一个数据库中而且完成他们将损坏利用能被挪动、拷贝和删除文件的全体“好办法”。

速率影响对INSERT和UPDATE语句是可骇的，而且在这类情形下几近一切的FOREIGNKEY反省都是无用的，由于不论如何你一般以准确的按次在准确的表中拔出纪录。

当更新一张表时，也有在很多表上保留锁的需求，由于反作用能够串连经由过程全体数据库。起首从一张表中删除纪录而且随后从其他表中删除他们，这更快。

你不再能够经由过程做一个周全的表删除并随后恢复一切的纪录的办法来恢复一张表(重新来历或从一个备份)。

假如你有外键，你不克不及倾倒和恢复表，除非你以一个十分特定的做这些。

很简单做一个“同意的”的轮回界说使得不成能用一个单个create语句重修每个表，就算界说可行又可用。



FOREIGNKEY独一好的方面是它给ODBC和一些其他客户程序反省一张表怎样被毗连的才能，而且利用它们显现出毗连图表并匡助机关使用。

MySQL不久将存储FOREIGNKEY界说以便一个客户能扣问并收到本来的毗连怎样举行的一个谜底。以后的“.frm文件格局没有它应有的位置。

5.4.6视图
MySQL不撑持视图，可是它在TODO上。

5.4.7--作为一个正文的入手下手
有些其他SQL数据库利用--入手下手正文。MySQL有“#”作为入手下手数正文的字符，即便mysql命令行工具删除以--入手下手的一切行。你也能够在MySQL中利用C正文作风/*thisisacomment*/。见7.29正文句法。

MySQL3.23.3和以上版本撑持--正文作风，只需正文跟在一个空格以后。这是由于这类退步的正文作风已引发用像以下代码那样的主动天生的SQL查询的很多成绩，这里我们主动地为!payment!拔出付出值：

UPDATEtbl_nameSETcredit=credit-!payment!
你想出当payment的值是负的时将产生甚么吗？

由于1--1在SQL中是正当的，我们以为--入手下手正文是可骇的。

但是在MySQL3.23中，你可以使用：1--Thisisacomment

假如你正在运转一个比3.23早的MySQL的版本，以下的会商才触及你：

假如你在一个文本文件中有一个SQL程序，它包括--正文，你应当利用：

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

而不是一般：
shell>mysqldatabase<text-file-with-funny-comments.sql

你也能够“现场”编纂命令文件将--正文改成#正文：

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

用这个命令改回他们：

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

5.5MySQL遵守甚么尺度？
EntrylevelSQL92。ODBClevel0-2。

5.6如何处置没有COMMIT/ROLLBACK
MySQL不撑持COMMIT-ROLLBACK。成绩是无效地处置COMMIT-ROLLBACK将必要完整分歧于MySQL明天利用的表结构。MySQL也将必要分外的线程在表上做主动扫除事情，并且磁盘用量将更高。这将使MySQL比当今慢上约莫2-4倍。MySQL比几近一切其他SQL数据库都快(一样平常最少快2-3倍)。缘故原由之一就是短少COMMIT-ROLLBACK。

今朝，我们是更多地完成SQL服务器言语(象存储历程)，有了它，你将的确很少必要COMMIT-ROLLBACK，这也将失掉更好的功能。

一般必要事件的轮回能够借助LOCKTABLES举行编码，而且当你能立即地更新纪录时，你不必要光标(cursor)。

我们在TODO上有事件和光标，但是并不是相称优先。假如我们完成这些，将作为CREATETABLE的选项，那意味着COMMIT-ROLLBACK将仅事情在那些表上，以便速率丧失仅仅强加在那些表上。

我们在TcX有一个更年夜的需求，一个比100%通用数据库的真正疾速的数据库。不管什么时候我们发明一个办法来完成这些特性而没有任何速率丧失，我们将大概做它。临时，有很多更主要的事变要做。反省TODO，看我们此时怎样将事变优先分列。（有的较初级别撑持的客户能够改动它，因而事变是能够从头优先化的。）

以后的成绩实践上是ROLLBACK，没有ROLLBACK，你能用LOCKTABLES做任何COMMIT举措。为了撑持ROLLBACK，MySQL将必需被改动以存储一切的旧纪录，假如收回ROLLBACK，它们被更新的而且将任何器材恢复到出发点。关于复杂的情况，这不是难做的(以后isamlog能够用于此目标)，可是为ALTER/DROP/CREATETABLE完成ROLLBACK将是更坚苦的。

制止利用ROLLBACK，你可使用以下战略：

利用LOCKTABLES...锁居处有你想要存取的数据库表。
测试前提。
假如统统无误，更新。
利用UNLOCKTABLES开释你的锁。
这一般比利用大概带ROLLBACK的买卖是一个更快的办法，只管不老是如许。这个办理计划不克不及处置的独一情况是当或人在更新傍边杀逝世线程时。在这类情形下，一切的锁将被开释，可是一些变动不克不及被实行。

你也可以使函数以单个操纵更新纪录。你能经由过程利用以下手艺失掉一个很无效率的使用程序：

绝对他们确当前的值修正字段
仅仅更新那些实践上变动的字段
比方，当我们正在更新一些客户信息时，我们仅仅更新那些改动了的客户数据并只测试没有任何数据的改动，或数据取决于改动的数据，与本来的行比拟变更了。关于改动了的数据的测试用WHERE子句在UPDATE语句中完成。假如纪录没被更新，我们给客户一条动静：“你改动了的一些数据已被其他用户改动了”，然后我们在一个窗口中显现新行对比旧行，因而用户能决意他该利用哪一个版本的客户纪录。

这给了我们相似于“列锁定”的器材，可是实践上乃至更好，由于我们仅仅更新某些列，利用相对他们确当前值的值。这意味着典范的UPDATE语句看上往象这些一样器材：

UPDATEtablenameSETpay_back=pay_back+relativechange;

UPDATEcustomer
SET
customer_date=current_date,
address=newaddress,
phone=newphone,
money_he_owes_us=money_he_owes_us+new_money
WHERE
customer_id=idANDaddress=oldaddressANDphone=oldphone;

正如你能看到的，这是很无效的而且就算其他客户已改动了pay_back或money_he_owes_us列的也能事情。

在很多情形下，为办理一些表格的独一标识符目标，用户已想要ROLLBACK或LOCKTABLES。这可用一个AUTO_INCREMENT列和一个SQL函数LAST_INSERT_ID()或CAPI函数mysql_insert_id()更高效地处置。见20.4.29mysql_insert_id()。

在TcX，我们历来没有任何对行级锁定的需求，由于我们老是能经由过程编码办理它。一些情形下必要的确行锁定，可是他们是很少见的。假如你想要行级锁定，你能够在表中利用标记列而且如许做：

UPDATEtbl_nameSETrow_flag=1WHEREid=ID;

假如行被找到发明而且row_flag在本来的行已不是1，对受影响的行数MySQL前往1。

你能够想到它，由于MySQL把下面的查询变成：

UPDATEtbl_nameSETrow_flag=1WHEREid=IDandrow_flag1;


义务编纂：eight(2001-06-0700:45)最近由权威调查机构Evans数据公司进行的一项调查显示，MySQL在过去两年已经获得了25%的市场份额。该调查公司还预测，相比其他的开源数据库和闭源数据库。

其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。

始终遗憾SQLServer的登陆无法分配CPU/内存占用等指标数。如果你的SQLServer给别人分配了一个只可以读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。

如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才能解决。

仓酷云

一个是把SQL语句写到客户端,可以使用DataSet进行加工;

一个是把SQL语句写到客户端,可以使用DataSet进行加工;

代替了原来VB式的错误判断。比Oracle高级不少。

可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

代替了原来VB式的错误判断。比Oracle高级不少。

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。