mysql 一些技巧

http://www.uplinux.com/download/doc/mysql/mysql4/06-4.html

在很多分页的程序中都这样写:

SELECT COUNT(*) from `table` WHERE ......; 查出符合条件的记录总数

SELECT * FROM `table` WHERE ...... limit M,N;查询当页要显示的数据

这样的语句可以改成:

SELECT SQL_CALC_FOUND_ROWS * FROM `table` WHERE ...... limit M, N;
SELECT FOUND_ROWS();
这样只要执行一次较耗时的复杂查询可以同时得到与不带limit同样的记录条数

第二个 SELECT返回一个数字，指示了在没有LIMIT子句的情况下，第一个SELECT返回了多少行 (若上述的 SELECT语句不包括 SQL_CALC_FOUND_ROWS 选项,则使用LIMIT 和不使用时，FOUND_ROWS() 可能会返回不同的结果）。

------------------------------------------------------------------------------------------------------------------------------

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] rows | rows OFFSET offset]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]

-------------------------------------------------------------------------------

SELECT CONCAT(last_name,', ',first_name) AS full_name   FROM mytable ORDER BY full_name;

group_concat();

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;

DISTINCT、DISTINCTROW 和 ALL 选项指定重复的记录行是否被返回。缺省为 (ALL)，返回所有匹配的记录行。DISTINCT 和 DISTINCTROW 是同义词，它指定结果集重复的记录行被排除。

所有以 SQL_ 开头、STRAIGHT_JOIN 和 HIGH_PRIORITY 的选项是 MySQL 对 ANSI SQL 的扩展。

HIGH_PRIORITY 将给 SELECT 语句比更新一个表有更高的优先级。你只应该对非常快的或需要立即返回的查询使用它。 如果一个表已被读锁定，甚至是有一个更新语句正在等待表的释放，一个 SELECT HIGH_PRIORITY 查询也将会执行。

SQL_BIG_RESULT 可以与 GROUP BY 或 DISTINCT 一同使用，以告诉优化器结果集将有许多记录行。在这种情况下，如果需要，MySQL 将直接使用基于磁盘的临时表。同样的，在这种情况下，MySQL 更愿意以 GROUP BY 上的一个键进行排序而不是建立一个临时表。

SQL_BUFFER_RESULT 将强制把结果放入一个临时表。这将有助于 MySQL 尽早地释放表和有助于将大的结果集传送到客户端。

SQL_SMALL_RESULT, 一个 MySQL 特有的选项，可以与 GROUP BY 或 DISTINCT 一同使用，以告诉优化器结果集将会很小。在这种情况下，MySQL 将使用快速的临时表存储结果表，而不是使用排序。在 MySQL 3.23 中，这通常是不需要的。

SQL_CALC_FOUND_ROWS (版本 4.0.0 和更新的) 告诉 MySQL 计算在不考虑 LIMIT 子句时结果集中将有多少行记录。然后使用 SELECT FOUND_ROWS() 可以检索到记录行的数目。查看章节 6.3.6.2 辅助功能函数。 请注意，在早于 4.1.0 的版本中，LIMIT 0 是不工作的，它将被优化为立即返回(结果集的记录数为 0)。查看章节 5.2.8 MySQL 如何优化 LIMIT。

如果你使用了 QUERY_CACHE_TYPE=2 (DEMAND)，SQL_CACHE 告诉 MySQL 将存储查询结果放入查询高速缓存内。查看章节 6.9 MySQL 的查询高速缓存。

SQL_NO_CACHE 告诉 MySQL 不允许将查询结果存储到查询缓存内。查看章节 6.9 MySQL 的查询高速缓存。

如果使用了 GROUP BY，输出记录将会依照 GROUP BY 列进行排序，就好像你对所有 GROUP BY 中的所有字段使用了 ORDER BY。MySQL 扩展了 GROUP BY 的用法，所以你也可以在 GROUP BY 中指定 ASC 和 DESC：

SELECT a,COUNT(b) FROM test_table GROUP BY a DESC

MySQL 扩展了的 GROUP BY 用法允许你选取没有在 GROUP BY 子句中提及的字段。如果你的查询没有得到你所期望的结果，请查看 GROUP BY 中的描述。查看章节 6.3.7 用于 GROUP BY 子句的函数。

STRAIGHT_JOIN 强制优化器以表在 FROM 子句中列出的顺序联结。如果优化器以一个非优化的次序联结各表，你可以使用它来加速一个查询。查看章节 5.2.1 EXPLAIN 句法(得到有关 SELECT 的信息)。

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10;  # 检索记录行 6-15

为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; # 检索记录行 96-last.

如果只给定一个参数，它表示返回最大的记录行数目：

mysql> SELECT * FROM table LIMIT 5;     # 检索前 5 个记录行

换句话说，LIMIT n 等价于 LIMIT 0,n。

SELECT ... INTO OUTFILE 'file_name' 格式的 SELECT 将选择的记录行写入一个文件。文件被建立在服务器主机上，并且不可以是已存在的 (不管别的，这可以防止数据库表和文件例如 `/etc/passwd' 被破坏)。你必须在服务器主机上有 FILE 权限来使用这个形式的 SELECT。 SELECT ... INTO OUTFILE 主要是有意于让你能够在服务主机上快速地转储一个表。如果你希望将结果文件建立在其它的主机上，而不是服务器上，你就不能使用 SELECT ... INTO OUTFILE。在这种情况下，你应该使用某些客户端程序例如 mysqldump --tab 或 mysql -e "SELECT ..." > outfile 产生文件来代替它。 SELECT ... INTO OUTFILE 是 LOAD DATA INFILE 的逆操作；语句中的 export_options 部分的句法由 FIELDS 和 LINES 子句组成，它们与与用在 LOAD DATA INFILE 语句中的相同。查看章节 6.4.9 LOAD 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 句法，所以其它是不需要转义。 下面的例子得到的文件是可用于许多老程序的格式。

SELECT a,b,a+b INTO OUTFILE "/tmp/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "/n"
FROM test_table;

如果使用 INTO DUMPFILE 代替 INTO OUTFILE，MySQL 将在文件中只写一行，没任何列或行端接和任何转义。如果你希望存储一个 blob 列到文件中，这是非常有用的。

注意，任何由 INTO OUTFILE 和 INTO DUMPFILE 创建的文件将被所有用户可读写！原因是， MySQL 服务器不能够创建一个其他用户拥有的文件，(你决不应该以 root 身份运行 mysqld)，该文件必须是公共可读写的，以便于你能操作它。

如果你以页/行锁使用在一个存储引擎上 FOR UPDATE，被检索的记录行将被写锁