mysql5.1学习笔记
时间：2006-07-14 来源：jingzhi
1.8.4. MySQL对标准SQL的扩展
MySQL服务器包含一些其他SQL DBMS中不具备的扩展。注意，如果使用了它们，将无法把代码移植到其他SQL服务器。在某些情况下，你可以编写包含MySQL扩展的代码，但仍保持其可移植性，方法是用“/*... */”注释掉这些扩展。在本例中，MySQL服务器能够解析并执行注释中的代码，就像对待其他MySQL语句一样，但其他SQL服务器将忽略这些扩展。例如：
SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...
如果在字符“!”后添加了版本号，仅当MySQL的版本等于或高于指定的版本号时才会执行注释中的语法：
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
这意味着，如果你的版本号为3.23.02或更高，MySQL服务器将使用TEMPORARY关键字。
· 磁盘上的数据组织
MySQL服务器会将每个数据库映射到MySQL数据目录下的1个目录中，并将数据库中的表映射到数据库目录下的文件名。它具有下述含义：
o 如果操作系统的文件名区分大小写（如大多数Unix系统），当MySQL服务器运行在这类操作系统上时，数据库名和表名也区分大小写。
o 你可以使用标准的系统命令来备份、重命名、移动、删除、并拷贝由MyISAM或ISAM存储引擎管理的表。例如，要想重命名MyISAM表，可重命名表对应的.MYD、.MYI、以及.frm文件。
数据库、表、索引、列或别名能够以数字开头（但或许不能全部由数字构成）。
· 通用语言语法
o 可以使用“””或“’”括住字符串，而不仅是“’”。
o 在字符串中使用“\”作为转义字符。
o 在SQL语句中，可以使用“db_name.tbl_name”语法访问不同数据库中的表。某些SQL服务器提供了相同的功能，但调用该用户空间除外。MySQL服务器不支持表空间，如下述语句中使用的那样： CREATE TABLE ralph.my_table...IN my_tablespace.
o 不需要在GROUP BY部分命名所有选择的列。对于某些十分特殊但相当正常的查询，它能提供更好的性能。
o 可以与GROUP BY一起指定ASC和DESC。
o 能够在带有“:=”赋值操作符的语句中设置变量。
o mysql> SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg
o -> FROM test_table;
o mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
注：一下内容来自mysql4.1.0参考手册。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' CONCAT_WS(separator, str1, str2,...) CONCAT_WS() 支持 CONCAT 加上一个分隔符，它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间：
mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name"); -> 'First name,Second name,Last Name' mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name"); -> 'First name,Last Name'
6.3.6.1 位函数
MySQL 使用 BIGINT (64 位) 算法进行位运算，因而这些操作符有一个 64 位的最大范围。 | 位或
mysql> SELECT 29 | 15; -> 31 返回值是一个 64 位的无符号整数。 & 位与
mysql> SELECT 29 & 15; -> 13 返回值是一个 64 位的无符号整数。 ^ 位异或
mysql> SELECT 1 ^ 1; -> 0 mysql> SELECT 1 ^ 0; -> 1 mysql> SELECT 11 ^ 3; -> 8 返回值是一个 64 位的无符号整数。 XOR 在 MySQL 4.0.2 中被加入。 << 左移一个长长的数字(BIGINT)：
mysql> SELECT 1 << 2; -> 4 返回值是一个 64 位的无符号整数。 >> 右移一个长长的数字(BIGINT)：
mysql> SELECT 4 >> 2; -> 1 返回值是一个 64 位的无符号整数。 ~ 置反所有位：
mysql> SELECT 5 & ~1; -> 4 返回值是一个 64 位的无符号整数。 BIT_COUNT(N) 返回在参数 N 中嵌入的比特位数量：
mysql> SELECT BIT_COUNT(29); -> 4
1.8.5. MySQL与标准SQL的差别1.8.5.1. 子查询
MySQL 4.1支持子查询和导出表。“子查询”指的是嵌套在另一语句中的SELECT语句。“导出表”（未命名视图）是另一语句的FROM子句中的子查询。
从MySQL 4.1版起，可以使用联合或其他方法重写大多数子查询。
1.8.5.2. SELECT INTO TABLE
MySQL服务器不支持Sybase SQL扩展： SELECT ... INTO TABLE ....。但MySQL服务器支持标准的SQL语法INSERT INTO ... SELECT ...，它基本上相同。
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
作为备选方式，可以使用SELECT INTO OUTFILE ...或CREATE TABLE ... SELECT。
从5.0版开始，MySQL支持SELECT ... INTO，以及用户变量。在使用光标和局部变量的存储程序中也可以使用相同的语法。
1.8.5.3. 事务和原子操作
MySQL服务器（3.23至该系列的最高版本，所有4.0版本，以及更高版本）支持采用InnoDB和BDB事务存储引擎的事务。InnoDB提供了全面的ACID兼容性。
MySQL服务器中的其他非事务性存储引擎（如MyISAM）遵从不同的数据完整性范例，称之为“原子操作”。按照事务术语，MyISAM表总能高效地工作在AUTOCOMMIT=1模式下。原子操作通常能提供可比较的完整性以及更好的性能。
由于MySQL服务器支持两种范例，因而你能决定是否利用原子操作的速度更好地服务于你的应用程序，或使用事务特性。该选择可按表进行。
正如所阐述的那样，事务性和非事务性表类型之间的权衡主要取决于性能。事务性表对内存和磁盘空间的要求更高，CPU开销也更大。另一方面，多种事务性表类型，如InnoDB，也能提供很多显著特性。MySQL服务器的模块化设计允许同时使用不同的存储引擎，以满足不同的要求，并在所有情形下，提供最佳性能。
但是，即便使用非事务性MyISAM表，你将如何使用MySQL服务器的特性来保持严格的完整性呢？这些特性与事务性表类型相比又如何呢？
1. 如果应用程序采用了特定的编写方式，依赖于在关键情况下能够调用ROLLBACK而不是COMMIT，那么事务性类型更方便。使用事务，还能确保未完成的更新或崩溃的活动不被提交到数据库，能为服务器提供自动回滚的机会，并保存你的数据库。
如果使用非事务性表，MySQL服务器几乎在所有情况下均允许你解决潜在的问题，方式是在更新前进行简单检查，并运行检查数据库一致性的简单脚本，如果出现不一致性，该脚本能自动修复它或给出告警。注意，仅使用MySQL日志或增加额外日志，通常能完美地更正表，同时不会造成数据完整性损失。
2. 在很多情况下，能够对关键的事务更新进行重写，使之成为“原子”类型。一般而言，所有由事务解决的完整性问题均能用LOCK TABLES或原子更新解决，从而确保了服务器不会自动中断，后者是事务性数据库系统的常见问题。
3. 为了安全使用MySQL服务器，无论是否使用事务性表，仅需启用备份和二进制日志功能。这样，你就能解决使用其他事务性数据库系统时遇到的任何问题。无论使用的数据库系统是什么，启用备份总是个好主意。
事务范型有自己的优点和不足之处。很多用户和应用程序开发人员喜欢这类简单性，在出现问题时或必要时，通过代码解决问题。但是，即使你是原子操作范型的新手，或更熟悉事务，也请考虑非事务性表的速度益处，与经过优化调整的最快的事务性表相比，它的速度快3～5倍。
在完整性具有最高重要性的情况下，即使是对非事务性表，MySQL也能提供事务级别的可靠性和安全性。如果使用LOCK TABLES锁定了表，所有更新均将被暂时中止直至完整性检查完成。如果你获得了对某一表的READ LOCAL锁定（与写锁定相对），该表允许在表尾执行并行插入，当其他客户端执行插入操作时，允许执行读操作。新插入的记录不会被有读锁定属性的客户端看到，直至解除了该锁定为止。使用INSERT DELAYED，能够将插入项置于本地队列中，直至锁定解除，不会让客户端等待插入完成。
从我们赋与其名称的意义上，“原子”绝非不可思议的。它仅意味着，你能确信在每个特性更新运行的同时，其他用户不能干涉它，而且不会出现自动回滚（如果你不小心，对于事务性表，这种情况可能发生）。MySQL服务器还能保证不存在脏读。
下面列出了使用非事务性表的一些技术：
· 对于需要事务的循环，通常能使用LOCK TABLES进行编码，不需要光标来更新正在处理的记录。
· 要想避免使用ROLLBACK，可采取下述策略：
1. 使用LOCK TABLES锁定所有希望访问的表。
2. 执行更新前，测试必须为真的条件。
3. 如果一切正常，执行更新。
4. 使用UNLOCK TABLES解除锁定。
与使用具有回滚可能性的事务性表相比，它通常具有更快的速度，虽然并非始终如此。该解决方案唯一不能处理的情形是，在更新中途杀死了线程。在这种情况下，将释放所有锁定，但某些更新可能尚未执行。
· 也可以使用函数在单一操作中更新记录。采用下述技术，能获得效率很高的应用程序。
o 根据其当前值更改列。
o 仅更新出现实际变化的列。
例如，当我们更新某些客户信息时，仅更新已更改的客户数据，与原始行相比，仅测试已更改的数据或依赖于已更改数据的数据是否未出现变化。对于已更改数据的测试，它是通过UPDATE语句的WHERE子句完成的。如果记录未更新，将向客户端发出消息： “一些你改变的数据已被其他用户更改”。接下来，我们在窗口中给出了旧行和新行，以便用户决定使用哪个版本。
这给出了与列锁定类似的结果，但效果更好，使用相对于其当前值的值，仅更新了某些列。这意味着，典型的UPDATE语句与下面给出的类似：
UPDATE tablename SET pay_back=pay_back+125;
UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_owed_to_us=money_owed_to_us-125
WHERE
customer_id=id AND address='old address' AND phone='old phone';
它很有效，即使其他客户端更改了pay_back或money_owed_to_us列中的值，也能使用。
· 在很多情况下，用户希望将LOCK TABLES和／或ROLLBACK用于管理唯一ID。可以在不使用锁定功能或回滚的情况下，使用AUTO_INCREMENT列以及LAST_INSERT_ID() SQL函数或mysql_insert_id() C API函数，更有效地处理之。
我们通常能使用代码来处理行级锁定方面的需求。在某些情况下，实际上不需要它，InnoDB表支持行级锁定。通过MyISAM表，能够在表中使用标志列，并完成类似下面的操作：
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;
1.8.5.4. 存储程序和触发程序
对于MySQL，在5.0版本中实现了存储程序。
从5.0.2版开始，在MySQL中实现了基本的触发器功能，计划在MySQL 5.1中进一步发展它。
1.8.5.5. 外键
在MySQL服务器3.23.44和更高版本中，InnoDB存储引擎支持对外键约束的检查功能，这些约束包括CASCADE、ON DELETE和ON UPDATE。请参见
对于InnoDB之外的其他存储引擎，MySQL服务器能够解析CREATE TABLE语句中的FOREIGN KEY语法，但不能使用或保存它。未来将进行扩展，能够将这类信息保存到表规范文件中，以便能被mysqldump和ODBC检索。稍后，还将为MyISAM表实现外键约束。
外键增强为数据库开发人员提供了多项益处：
· 假定关联设计恰当，外键约束使得程序员更难将不一致性引入数据库。
· 数据库服务器具有集中式约束检查功能，因而没有必要在应用程序一侧执行这类检查。这样，就消除了不同应用程序使用不同方式检查约束的可能性。
· 使用级联更新和删除，简化了应用程序代码。
· 设计恰当的外键有助于以文档方式记录表间的关系。
请记住，这些好处是以数据库服务器为执行必要检查而需的额外开销为代价的。服务器额外检查会影响性能，对于某些应用程序，该特性不受欢迎，应尽量避免。（出于该原因，在一些主要的商业应用程序中，在应用程序级别上实施了外键逻辑）。
MySQL允许数据库开发人员选择要使用的方法。如果你不需要外键，并希望避免与强制引用完整性有关的开销，可选择另一种表类型取而代之，如MyISAM。（例如，MyISAM存储引擎为仅执行INSERT和SELECT操作的应用程序提供了极快的性能，这是因为插入能和检索同时进行）。
如果你不打算利用引用完整性检查具备的优点，请记住下述要点：
· 不存在服务器端外键关联检查时，应用程序本身必须处理这类关联事宜。例如，将行按恰当顺序插入表时应谨慎，并应避免产生孤立的子记录。必须能够在多记录插入操作期间更正出现的错误。
· 如果ON DELETE是应用程序所需的唯一引用完整性功能，请注意，从MySQL服务器4.0起，可以使用多表DELETE语句，用单一语句从多个表中删除行。
· 从具有外键的表删除记录时，在缺少ON DELETE的情况下，一种解决方式是为应用程序增加恰当的DELETE语句。实际上，它与使用外键同样快，而且移植性更好。
注意，使用外键在某些情况下会导致问题。
· 外键支持能处理很多引用完整性事宜，但仍需要仔细设计键的关系，以避免循环规则或不正确的级联删除组合。
· DBA需要创建关联拓扑，这会使从备份中恢复单独表变得困难，该类情形并不罕见。（加载依赖其他表的表时，MySQL允许你临时禁止外键检查，从而降低了该难度）。在MySQL 4.1.1以前。重新加载时，mysqldump能够生成自动利用该性能的转储文件。
注意，SQL中的外键用于检查和强制引用完整性，而不是联合表。如果打算用SELECT语句获取多个表的结果，可在表之间执行联合操作：
SELECT * FROM t1, t2 WHERE t1.id = t2.id;
ODBC应用程序常使用不带“ON DELETE ...”的FOREIGN KEY语法来生成自动WHERE子句。
1.8.5.6. 视图
在MySQL服务器5.0版中实现了视图功能（包括可更新视图）。在5.0.1和更高版本中，提供了二进制版的视图功能。
View（视图）十分有用，它允许用户像单个表那样访问一组关系（表），而且仅允许对它们的这类访问。视图也能限制对行的访问（特定表的子集）。对于列控制的访问，可使用MySQL服务器中的高级权限系统。
在设计视图的过程中，我们的宏伟目标是，在SQL的范围内尽可能与关联数据库系统的“Codd's Rule #6”兼容。“所有理论上可更新的视图，实际上也应是可更新的”。
1.8.5.7. ‘--’作为注释起始标记
一些其他SQL数据库采用“--”作为注释开始标志。MySQL服务器采用“#”作为注释起始字符。对于MySQL服务器，也能使用C风格的注释：/*该处为注释*/。
MySQL服务器3.23.3和更高版本支持“--”注释风格，但要求注释后面跟1空格（或控制字符，如新行）。之所以要求使用空格，是为了防止与自动生成SQL查询有关的问题，它采用了类似下面的代码，其中，自动为“!payment!”插入“payment”的值：
UPDATE account SET credit=credit-!payment!
考虑一下，如果“payment”的值为负数如“-1”时会出现什么情况：
UPDATE account SET credit=credit--1
在SQL中“credit--1”是合法的表达式，但是，如果“--1”被解释为注释开始，部分表达式将被舍弃。其结果是，表达式的意义与预期的意义完全不同。
UPDATE account SET credit=credit
该语句不会对值作任何更改！这表明，允许注释以“--”开始会产生严重后果。
采用MySQL服务器3.23.3和更高版本中的这类注释方法，“credit--1”实际上很安全。
另一个安全特性是，mysql命令行客户端将删除所有以“--”开头的行。
仅当使用高于3.23.3的MySQL时，下述信息才有意义：
如果有1个文本文件形式的SQL程序，该文件包含“--”注释，应按下述方式使用replace实用工具，将其转换为使用“#”字符的注释：
shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql db_name
而不是通常的：
shell> mysql db_name < text-file-with-funny-comments.sql
你也可以编辑注释文件，将“--”注释更改为“#”注释：
shell> replace " --" " #" -- text-file-with-funny-comments.sql
使用下述命令将其改回去：
shell> replace " #" " --" -- text-file-with-funny-comments.sql
相关阅读 更多 +
