MySQL 使用经验合辑

zhouying23

一. MySQL Dump:
命令行下具体用法如下：  mysqldump -u用戶名 -p密码 -d 數據库名 表名 脚本名;
1、导出數據库為dbname的表结构（其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql）
mysqldump -uroot -pdbpasswd -d dbname >db.sql;
2、导出數據库為dbname某张表(test)结构
mysqldump -uroot -pdbpasswd -d dbname test>db.sql;
3、导出數據库為dbname所有表结构及表數據（不加-d）
mysqldump -uroot -pdbpasswd  dbname >db.sql;
4、导出數據库為dbname某张表(test)结构及表數據（不加-d）
mysqldump -uroot -pdbpasswd dbname test>db.sql;
EG: 
　　mysqldump  -h XXXX.XXX.XXX.XX -uUSERNAME_XXXX -pPASSWORD_XXX DBNAME TABLENAME>file.sql;
 
二. MySQL查询随机一行数据:

SELECT AAA.ID, AAA.Name, AAA.Age
FROM MySqlTable AS AAA JOIN (
SELECT ROUND(RAND() *
((SELECT MAX(ID) FROM MySqlTable)-(SELECT MIN(ID) FROM MySqlTable))+
(SELECT MIN(ID) FROM MySqlTable)
) AS RandomID) AS BBB
WHERE AAA.ID >= BBB.RandomID
ORDER BY AAA.ID LIMIT 1;
 在7W的数据量下,  查询平均花费 0.0014 秒(已测)
 
三. MySQL 查询缓存:select @@query_cache_type;
1. 要么打开缓存;
2. 要么关闭缓存;
3. 要么使用SQL的显示缓存 select SQL_CACHE user_name from users where user_id = '100';
http://blog.sina.com.cn/s/blog_75ad10100101by7j.html
 
四. MySQ Lleft(right)join筛选条件在on and与where差异:
http://xianglp.iyunv.com/blog/868957
 
五. MySQL计算集合之间的差集和交集;
普通做法就是IN; 但是大数据下太慢; 所以1, 2:
1. 差集;

SELECT ID FROM (
SELECT DISTINCT A.AID AS ID FROM TABLE_A A
UNION ALL
SELECT DISTINCT B.BID AS ID FROM TABLE_B B
)TEMP GROUP BY ID HAVING COUNT(ID) = 1
 2. 交集;

SELECT ID FROM (
SELECT DISTINCT A.AID AS ID FROM TABLE_A A
UNION ALL
SELECT DISTINCT B.BID AS ID FROM TABLE_B B
)TEMP GROUP BY ID HAVING COUNT(ID) = 2
 3. .... LEFT JOIN解决问题:
http://www.cnblogs.com/cy163/archive/2008/11/03/1325400.html
 
六. MySQL死锁解决:
JAVA异常:
　　2014-02-11 12:20:29,947 - SYS [http-bio-52000-exec-9] ERROR  Log4jUtil.exception(Log4jUtil.java:47) 
　　- PreparedStatementCallback; SQL []; Deadlock found when trying to get lock; 
　　try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock;  try restarting transaction
　　mysql> show processlist ;
　　mysql> kill mysqlprocessid;
　　原因: 数据批量更新时,执行一个Update语句时, 使用了一个耗时较大的SQL, SQL中用了IN, IN中的数据大约是2000条;
 
七. mysql group by order by

select * from test group by category_id order by `date`
 
select * from (select * from `test` order by `date` desc) `temp`  group by category_id order by `date` desc
第一个SQL,是基于 category_id分组后, 取分组的数据为顺序排列的第一条数据.  最后在根据每个组的数据基于date排序;

第二个SQL, 是取category_id分组中date最大的值的行, 最后再根据每个组基于date排序

 http://www.cnblogs.com/not-code/archive/2011/11/01/2231493.html

 

八. 循环插入表数据

#创建测试的test表
DROP TABLE IF EXISTS test;
CREATE TABLE test(
ID INT(10) NOT NULL,
`Name` VARCHAR(20) DEFAULT '' NOT NULL,
PRIMARY KEY( ID )
)ENGINE=INNODB DEFAULT CHARSET utf8;
#创建生成测试数据的存储过程
DROP PROCEDURE IF EXISTS pre_test;
DELIMITER //
CREATE PROCEDURE pre_test()
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
WHILE i<10000000 DO
INSERT INTO test ( ID,`Name` ) VALUES( i, CONCAT( 'Carl', i ) );
SET i = i+1;
IF i%2000 = 0 THEN
COMMIT;
END IF;
END WHILE;
END; //
DELIMITER ;
#执行存储过程生成测试数据
CALL pre_test();


 

九. 查询排名:

/*表结构*/
CREATE TABLE  `t1` (                  
`xingming` char(10) default NULL,   
`chengji` decimal(6,2) default NULL,
`mingci` int(3) NOT NULL,            
KEY `t1` (xingming,chengji)            
) ;
/*数据*/
insert into t1 (xingming,chengji)
values
('张一',50),
('张二',80),
('张三',70),
('张四',20),
('张五',90),
('张六',90),
('张七',70);
/*思路
排名是这样的
先按分数排序
如果分数一样,则并列第几名
分数不一样,则是他的顺序号
用三个变量实现
@chengji  保存成绩
@mingci 保存名次
@shunxu 保存顺序
*/
set @chengji=0;
set @mingci=1;
set @shunxu=0;
/*sql语句*/
update t1,
(select xingming,chengji,
@shunxu:=@shunxu+1,
if(chengji=@chengji,@mingci,@mingci:=@shunxu) as mingci,
@chengji:=chengji
from t1 order by chengji desc) as t2
set t1.mingci=t2.mingci
WHERE t1.xingming=t2.xingming and t1.chengji=t2.chengji;

select xingming,chengji,mingci from t1 order by mingci desc;
/*查询结果
"张四""20.00""7"
"张一""50.00""6"
"张三""70.00""4"
"张七""70.00""4"
"张二""80.00""3"
"张五""90.00""1"
"张六""90.00""1"
*/
 同理, 给每行数据返回中添加自增的ID的SQL;

SET    @VAR_Rank=0;
SELECT AA.DataColumn,
@VAR_Rank:=@VAR_Rank+1 AS ID
FROM   TABLE_XXXXX AA
where  AA.DataColumn>1000
ORDER  BY AA.DataColumn ASC
LIMIT  100
 http://bbs.csdn.net/topics/250014224


   十. 数据库表数据备份:

#!/bin/bash
da=`date '+%Y%m%d'`
#echo "$da"
mysqldump -udbusername -ppasswd110 -h127.0.0.1 dbname tablename > /data/tablename/tablename_$da.sql