mysql实用技巧

 

http://blog.csdn.net/fenglibing/archive/2007/10/24/1841537.aspx

 

 

---

MYSQL的BENCHMARK函数
MYSQL中提供了一个计算表达式性能的函数BENCHMARK
BENCHMARK(count,expr)
BENCHMARK会重复计算expr表达式count次，通过这种方式就可以评估出mysql执行这个expr表达式的效率。这个函数的返回值始终是0，但可以根据客户端提示的执行时间来得到BENCHMARK总共执行的所消耗的时间，如以下这个例子
mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));

+----------------------------------------------+

| BENCHMARK(1000000,ENCODE('hello','goodbye'))

|+----------------------------------------------+| 0

|+----------------------------------------------+

1 row in set (4.74 sec)
上面例子中的4.74 秒指的是在mysql客户端总共消耗的时间。
BENCHMARK函数只能测量数字表达式(scalar expression)的性能，虽然说表达式可以是一个子查询，但子查询返回的只能是单个值。在BENCHMARK(10, (SELECT * FROM t)) 这个语句中，如果t表有多列或是t表中记录多于1行的话这个函数就会执行失败。BENCHMARK函数在执行多次的过程中sql的解析(parser)、优化(optimizer)、锁表(table locking)等操作只会进行一次，只有运行评估(runtime evaluation)会执行count次。 利用BENCHMARK，mysql就可以自动为我们多次执行表达式计算，从而获取比较平均的计算结果。

---

MYSQL EXPLAIN语句的extended 选项学习体会

mysql中有一个explain 命令可以用来分析select 语句的运行效果，例如explain可以获得select语句使用的索引情况、排序的情况等等。

除此以外，explain 的extended 扩展能够在原本explain的基础上额外的提供一些查询优化的信息，这些信息可以通过mysql的show warnings命令得到。下面是一个最简单的例子。首先执行对想要分析的语句进行explain，并带上extended选项mysql> explain extended select * from account/G;

*************************** 1. row ***************************

id: 1select_type: SIMPLE table: account type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra:1 row in set, 1 warning (0.00 sec)

接下来再执行Show Warningsmysql> show warnings/G;

*************************** 1. row ***************************

Level: Note Code: 1003Message: select `dbunit`.`account`.`id` AS `id`,`dbunit`.`account`.`name` AS `name` from `dbunit`.`account`

1 row in set (0.00 sec)

从 show warnings的输出结果中我们可以看到原本的select * 被mysql优化成了 select `dbunit`.`account`.`id` AS `id`,`dbunit`.`account`.`name` AS `name`。 explain extended

除了能够告诉我们mysql的查询优化能做什么，同时也能告诉我们mysql的查询优化做不了什么。Mysql performance的Extended EXPLAIN这篇文中中作者就利用explain extended +show warnings 找到了mysql查询优化器中不能查询优化的地方。

从 EXPLAIN extended SELECT * FROM sbtest WHERE id>5 AND id>6 AND c="a" AND pad=c语句的输出我们得知mysql的查询优化器不能将id>5 和 id>6 这两个查询条件优化合并成一个 id>6。 在mysql performance的explain extended文章中第三个例子和静室的explain的extended选项文章中，两位作者也对explain extended做了进一步的实验，从这个两篇文中中我们可以得出结论是从explain extend的输出中，我们可以看到sql的执行方式，对于分析sql还是很有帮助的。

下面特别摘抄了静室的explain的extended选项这篇文章中的内容

/******************************以下代码和分析摘抄至静室的explain的extended选项**************/

mysql>explain extended select * from t where a in (select b from i);

+----+--------------------+-------+------+|

id | select_type | table | type |

+----+--------------------+-------+------+|

1 | PRIMARY | t | ALL || 2 | DEPENDENT SUBQUERY | i | ALL |

+----+--------------------+-------+------+2

rows in set, 1 warning (0.01 sec)

 

子查询看起来和外部的查询没有任何关系，为什么MySQL显示的是DEPENDENT SUBQUERY，和外部相关的查询呢?从explain extended的结果我们就可以看出原因了。

mysql>show warnings/G

*************************** 1. row ***************************

Level: NoteCode: 1003Message: select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`from `test`.`t` where(`test`.`t`.`a`,(select 1 AS `Not_used` from `test`.`i`where ((`test`.`t`.`a`) = `test`.`i`.`b`)))1 row in set (0.00 sec)在这里MySQL改写了SQL，做了in的优化。

/******************************以上代码和分析摘抄至静室的explain的extended选项*********************/ 不过需要注意的一点是从EXPLAIN extended +show warnings得到“优化以后”的查询语句可能还不是最终优化执行的sql，或者说explain extended看到的信息还不足以说明mysql最终对查询语句优化的结果。同样还是mysql formance的explain Extended这篇文章的第二个例子就说明了这种情况/*****************************************************************************************************/

mysql> EXPLAIN extended SELECT t1.id,t2.pad FROM sbtest t1, sbtest t2 WHERE t1.id=5 AND t2.k=t1.k;

+----+-------------+-------+-------+---------------+---------+---------+-------+-------+-------+|

id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+-------+-------+-------+|

1 | SIMPLE | t1 | const | PRIMARY,k | PRIMARY | 4 | const | 1 | || 1 | SIMPLE | t2 | ref | k | k | 4 | const | 55561 | |+----+-------------+-------+-------+---------------+---------+---------+-------+-------+-------+

2 rows IN SET, 1 warning (0.00 sec) mysql> SHOW warnings /G*************************** 1. row ***************************Level: NoteCode: 1003Message: SELECT `test`.`t1`.`id` AS `id`,`test`.`t2`.`pad` AS `pad` FROM `test`.`sbtest` `t1`JOIN `test`.`sbtest` `t2` WHERE ((`test`.`t2`.`k` = `test`.`t1`.`k`) AND (`test`.`t1`.`id` = 5)) 1 row IN SET (0.00 sec)/*************************************************************************************************/

 

从Explain的结果中我们可以得到t1表的查询使用的是"const"类型，也就是说mysql查询的时候会先由t1.id=5 找到t1.k 再利用t1.k的值去t2表中查询数据，很显然这样的查询优化结果没有在接下来的Show Warings输出中找到。

 

总结 还是引用静室 在explain的 extended选项这篇文章中的几句话"从explain extend的输出中，我们可以看到sql的执行方式，对于分析sql还是很有帮助的"。

 

---

MYSQL 客户端常用技巧

 

这是关于【MYSQL 客户端常用技巧】这组文章的第一篇，主要来源是参考了 http://phpadvent.org/2008/mysql-client-tips-by-ligaya-turmelle。在第二篇中会介绍mysql客户端在linux环境下一些常用的命令和技巧。
1./G
指定输出以“竖排”的格式输出结果。通常的“横排”方式往往由于输出换行导致观察结果比较困难。结果就清晰很多了:-)

2./c 终止当前命令
打错命令是常用的事，/c可以用来帮你终止当前的输入，重新新的命令。
有时候输入的过程中会出现单引号不匹配的情况，直接/c是不能结束命令的，需要先用‘结束，然后再使用/c。仔细观察一下mysql客户端的提示符，当出现单引号不匹配的情况时，mysql客户端的提示符就变了了'>
mysql> seleclt * from
    -> table wher a='
    '> /c
    '> '
    -> /c
3 tee and notee
tee用来定义讲mysql客户端的屏幕输出一并输出到到指定的文件中，这一点跟linux下的tee命令是一样的。
notee 顾名思义是停止这种输出。
mysql> TEE c:/output.txt   --把屏幕输出一并输出到c:/output.txt文件中
mysql> select * from tables; --这个命令的输出就会一并输出到c:/output.txt文件
mysql> NOTEE --停止输出
注意tee 可以用快捷方式/T来替换；notee用快捷方式/t来替换

4. help content
当你有记不住sql命令的时候还在通过msyql的online或offline的参考来查找资料吗。mysql 客户端内置的帮助命令或许可以帮你解燃眉之急。
mysql> help alter table

一、使用readline快捷方式
mysql的命令行也使用了readline。下面整理一些常用的readline命令
快捷方式    说明   
Ctrl-b    左移一个字符   
Ctrl-f    右移一个字符   
Alt-b    左移一个词   
Alt-f    右移一个词   
Ctrl-a    移到行头   
Ctrl-b    移到行尾   
Ctrl-d    删除当前字符   
Ctrl-u    删除当前光标到行首   
Ctrl-k    删除当前光标到行尾   
Ctrl-w    删除当前光标的前面一个字   
Ctrl-y    粘贴之前删除的文字   
Ctrl-p    上一个命令   
Ctrl-n    下一个命令   
Ctrl-r    向前搜索命令记录   
Ctrl-s    向后搜索命令记录   
Ctrl-J    停止搜索命令记录   
Ctrl-G    停止搜索命令记录回复原始输入             
Ctrl-_    撤销最近的一次编辑命令
Ctrl-l    清屏，将当前行置顶

二、edit 或/e
在linux下 用edit或/e可以直接调出linux下的编辑器（Vi等）来编辑命令行的输入。这种模式对哪些比较复杂的sql语句尤其有使用价值，一方面省却了重复输入大段sql，另一方面可以使用编辑器上更为丰富的编辑命令来提高编辑效率。

 

---

mysql中replace的用法
1.replace into
replace into table (id,name) values('1','aa'),('2','bb')
此语句的作用是向表table中插入两条记录。如果主键id为1或2不存在
就相当于
insert into table (id,name) values('1','aa'),('2','bb')
如果存在相同的值则不会插入数据
2.replace(object,search,replace)
把object中出现search的全部替换为replace
select replace('www.163.com','w','Ww')--->WwWwWw.163.com
例：把表table中的name字段中的aa替换为bb
update table set name=replace(name,'aa','bb')

---

ON DUPLICATE KEY UPDATE注意事项
INSERT INTO ipstats VALUES('192.168.0.1', 1) ON DUPLICATE KEY UPDATE clicks=clicks+1;
相当于：
IF (SELECT * FROM ipstats WHERE ip='192.168.0.1') {
   UPDATE ipstats SET clicks=clicks+1 WHERE ip='192.168.0.1';
} else {
   INSERT INTO ipstats (ip, clicks) VALUES ('192.168.0.1', 1);
}
注意，要使用这条语句，前提条件是这个表必须有一个唯一索引或主键。

---

关于SQL语句Count的一点细节
count语句支持*、列名、常量、变量,并且可以用distinct关键字修饰， 并且count(列名)不会累计null的记录。下面随便用一些例子示范一下count的规则：比如对如下表做统计，所有列这里都用sql_variant类型来表示。
if (object_id ('t_test' )> 0 )
drop table t_test
go
create table t_test (a sql_variant , b sql_variant , c sql_variant )
insert into t_test select 1 , 1 , 'a'
insert into t_test select 1 , getdate (), null
insert into t_test select 'a' , null , 1
insert into t_test select 3 , null , null
insert into t_test select null , null , null
go
select * from t_test
go
select
count (* ) --总数
, count (nullif (1 , 1 )) --永远返回0
, count (a ) --a数量
, count (b) --b数量
, count (distinct a ) --a不重复数量
, count (isnull (b, c )) --b或者c不为null数量
, count (Coalesce (a , b, c )) --a或者b或者c不为null数量
, count (nullif (a , b)) --a不等于b的数量
, count (nullif (isnumeric (cast (a as varchar (38 ))), 0 ))--a是数字的数量
from t_test
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/jinjazz/archive/2009/09/16/4558900.aspx

 

---

mysql 3表连接，我的写法是
select * from k left join t left join y on k.kid=t.kid on t.id=y.id;
回来一试原来错了对的写法是：

 

select * from k left join (t,y) on (k.kid=t.kid and t.id=y.id);

---

mysql临时表
创建临时表的语句如下：
CREATE TEMPORARY TABLE tmp_table (
   name VARCHAR(10) NOT NULL,
   value INTEGER NOT NULL
)
而直接将查询结果导入临时表的创建语句是：
CREATE TEMPORARY TABLE tmp_table SELECT * FROM tables
临时表创建好之后就可以查询了。在每个mysql链接断开时临时表会自动删除，手工删除临时表的语句和删除普通表一样：
DROP TABLE tmp_table
另外还有一种临时表HEAP表
如果你声明临时表是一个HEAP表，MySQL也允许你指定在内存中创建它：
CREATE TEMPORARY TABLE tmp_table (
   name VARCHAR(10) NOT NULL,
   value INTEGER NOT NULL
) TYPE = HEAP
因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。然而，HEAP表与一般的表有些不同，且有自身的限制。详见MySQL参考手册。
正如前面的建议，你应该测试临时表看看它们是否真的比对大量数据库运行查询快。如果数据很好地索引，临时表可能一点不快。

---

mysql事务处理实例
$handler=mysql_connect("localhost","","");
mysql_select_db("task");
mysql_query("SET AUTOCOMMIT=0");//设置为不自动提交，因为MYSQL默认立即执行
mysql_query("BEGIN");//开始事务定义
if(!mysql_query("insert into trans (id) values('2')"))
{
   mysql_query("ROOLBACK");//判断当执行失败时回滚
}
if(!mysql_query("insert into trans (id) values('4')"))
{
   mysql_query("ROOLBACK");//判断执行失败回滚
}
mysql_query("COMMIT");//执行事务
mysql_close($handler);

 

---

多表更新
UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
注意：多表 UPDATE 不可以使用 ORDER BY 或 LIMIT。
INSERT... ON DUPLICATE KEY UPDATE: 作用：插入数据，如果有重复的primate key 则执行update后面的语句
insert into local (id, count) select id, count from remote where status = 1 ON DUPLICATE KEY UPDATE count = remote.count, status = 1
如果要同步数据，或者说要将remote的new record copy到local中去，就要用第二种方法了。第一种方法是没办法insert 新记录的
多表删除
第一个多表删除格式从 MySQL 4.0.0 开始被支持。第二个多表删除格式从 MySQL 4.0.2 开始被支持。
仅仅在 FROM 或 USING 子句 之前 列出的表中的匹配记录行被删除。效果就是，你要以从多个表中同时删除记录行，并且同样可以有其它的表用于检索。
在表名后的 .* 仅仅是为了兼容 Access：
DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id 或
DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id
在上面的情况下，我们仅仅从 t1 和 t2 表中删除匹配的记录行。
如果一个 ORDER BY 子句被使用(从 MySQL 4.0.0 开始支持), 记录行将以指定的次序删除。这实际上只有连同 LIMIT 一起才有用。示例如下：
DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp LIMIT 1
这将删除匹配 WHERE 子句的，并且最早被插入(通过 timestamp 来确定)的记录行。
DELETE 语句的LIMIT rows 选项是 MySQL 特有的，它告诉服务器在控制权被返回到客户端之前可被删除的最大记录行数目。这可以用来确保一个特定的 DELETE 命令不会占用太长的时间。你可以简单地重复使用 DELETE 命令，直到被影响的记录行数目小于 LIMIT 值。
从 MySQL 4.0 开始，在 DELETE 语句中可以指定多个表，用以从一个表中删除依赖于多表中的特殊情况的记录行。然而，在一个多表删除中，不能使用 ORDER BY 或 LIMIT。

---

用Tcpdump抓取MySQL执行的SQL
#!/bin/bash
tcpdump -i eth0 -s 0 -l -w – dst port 3306 | strings | perl -e ‘
#!/bin/bash
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i) {
if (defined $q) { print “$q/n”; }
$q=$_;
} else {
$_ =~ s/^[ /t]+//; $q.=” $_”;
}
}’

 

 

---

mySQL的timestamp类型自动更新问题

CREATE TABLE `test` ( `t1` timestamp NOT NULL default CURRENT_TIMESTAMP ) ENGINE=MyISAM ;
CREATE TABLE `test` ( `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM ;
而实际上，以上两个建表语句的效果是一样的：
比较之下，我的语句少了“on update CURRENT_TIMESTAMP”或多了“default CURRENT_TIMESTAMP”。如此一来，这个timestamp字段只是在数据insert的时间建立时间，而update时就不会有变化了。当然，如果你就是想达到这个目的倒也无所谓。

查找英文手册(http://dev.mysql.com/doc/refman/5.1/en/timestamp.html)，得到更详细的解说，翻译如下：
在CREATE TABLE语句中，第1个TIMESTAMP列可以用下面的任何一种方式声明：
1： 如果定义时DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句都有，列值为默认使用当前的时间戳，并且自动更新。
2： 如果不使用DEFAULT或ON UPDATE子句，那么它等同于DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。
3： 如果只有DEFAULT CURRENT_TIMESTAMP子句，而没有ON UPDATE子句，列值默认为当前时间戳但不自动更新。
4： 如果没用DEFAULT子句，但有ON UPDATE CURRENT_TIMESTAMP子句，列默认为0并自动更新。
5： 如果有一个常量值DEFAULT，该列会有一个默认值，而且不会自动初始化为当前时间戳。如果该列还有一个ON UPDATE CURRENT_TIMESTAMP子句，这个时间戳会自动更新，否则该列有一个默认的常量但不会自动更新。
换句话说，你可以使用当前的时间戳去初始化值和自动更新，或者是其中之一，也可以都不是。(比如，你在定义的时候可以指定自动更新，但并不初始化。)

 

---

TRUNCATE和DELETE的几点区别
1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。
2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
4、TRUNCATE不能触发任何DELETE触发器。
5、不能授予任何人清空他人的表的权限。 6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。
7、不能清空父表。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fenglibing/archive/2007/10/24/1841447.aspx

---

GROUP_CONCAT()妙用

语法：
GROUP_CONCAT([DISTINCT] expr [,expr ...][ORDER BY {unsigned_integer | col_name | expr}[ASC | DESC] [,col_name ...]][SEPARATOR str_val])

采用GROUP_CONCAT()函数和GROUP BY语句就显得非常简单了，如下所示：
mysql> SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
+------------+---------+
| student_id | courses |
+------------+---------+
| 2 | 3,4,5 |
+------------+---------+
1 row IN SET (0.00 sec)

分隔符还可以自定义，默认是以“,”作为分隔符，若要改为“|||”，则使用SEPARATOR来指定，例如：
SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
除此之外，还可以对这个组的值来进行排序再连接成字符串，例如按courses_id降序来排：
SELECT student_id, GROUP_CONCAT(courses_id ORDER BY courses_id DESC) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
详细出处参考：http://www.jb51.net/article/13985.htm

 

---

 

1、将一个表中的内容拷贝到另外一个表中
insert into testT1(a1,b1,c1) select a,b,c from test;
insert into testT select * from test; (前提是兩個表的結構完全相同)
insert into notebook(id,title,content)
select notebook_sequence.NEXTVAL,first_name,last_name from students;
注:a1,b1,c1是現在表中的字段名。A,b,c是原表中的字段名
拷贝单条数据
insert into a (select '123' as id, temp,temp2 from b where id=10)
2、查找重复记录
SELECT DRAWING,DSNO FROM EM5_PIPE_PREFAB WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND EM5_PIPE_PREFAB.DSNO=D.DSNO);
3、删除重复记录
DELETE FROM EM5_PIPE_PREFAB WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND EM5_PIPE_PREFAB.DSNO=D.DSNO);
4、復制表結構
在ORACLE中: create table new_table as select * from old_table where rownum < 1;
在SQL SERVER中:说明：复制表(只复制结构,源表名：a 新表名：b)
SQL: select * into b from a where 1<>1
语句
5、一次刪除多個字段
alter table TEST_A drop (TEST_num,test_name);
6、實現一個表的備份
create table tableName_bak as select * from tableName;
7、創建兩個表的主外鍵關聯
--先創建主表
create table t1(id int,stu_name varchar(50));
--為主表增加持久的主鍵約束
alter table t1 add(constraint t1_id primary key(id) deferrable);
--創建從表，并將從表的id設為主鍵
create table t2(id int primary key,sex varchar(10),address varchar(50));
--為從表增加外鍵約束，該約束來自于主表所創建的約束字段
--當從主表中刪除記錄時，自動刪除從表中與之相對的具有相同id的記錄
--當在從表中插入記錄時，不能夠插入在主表中沒有id的記錄
alter table t2 add(constraint t2_id_fk foreign key(id) references t1(id) on delete cascade deferrable);
--測試數據
insert into t1 values(1,'FLB');
insert into t1 values(2,'FLB1');
insert into t1 values(3,'FLB2');
insert into t1 values(4,'FLB3');
insert into t2 values(1,'boy','leijiang');
insert into t2 values(2,'boy','leijiang');
insert into t2 values(3,'boy','leijiang');
insert into t2 values(4,'boy','leijiang');
--下面這條記錄插入錯誤
insert into t2 values(5,'boy','leijiang');
--刪除主表中的記錄
delete t1;
--查看從表中記錄，已經沒有記錄，說明約束成功
select * from t2;
8、關于系統時間
--取得當前系統時間、當前月的最后一天，離當前月的結束還有几天
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last",LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL;
--取得當前星期几
select to_char(sysdate,'Day') from dual;
--取得當前月
select to_char(sysdate,'Month') from dual;
--取得指定日期的星期
select to_char(to_date('20020126','yyyymmdd'),'Day') from dual;
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fenglibing/archive/2007/10/24/1841537.aspx
-------------------------------------------------