Mysql应用总结

Mysql应用总结

1 层次树型查询

Mysql需要借助自定义函数getChildLst 和Mysql自带的FIND_IN_SET函数实现层次查询，ABC是数据库名称。

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Function structure for getChildLst
-- ----------------------------
DROP FUNCTION IF EXISTS `getChildLst`;
DELIMITER ;;
CREATE DEFINER=`ABC`@`%` FUNCTION`getChildLst`(rootId INT) RETURNS varchar(1000) CHARSET utf8
BEGIN
      DECLARE sTemp VARCHAR(1000);
      DECLARE sTempChd VARCHAR(1000); 
      SET sTemp = '$';
      SET sTempChd =cast(rootId as CHAR); 
      WHILE sTempChd is not null DO
        SET sTemp = concat(sTemp,',',sTempChd);
        SELECT group_concat(RULE_ID) INTO sTempChd FROM TB_RTA_CORE_CHECK_RULEwhere FIND_IN_SET(PARENT_ID ,sTempChd)>0;
      END WHILE;
      RETURN sTemp;
 END
;;
DELIMITER ;

查询示例语句：

SELECT  RULE_ID,PARENT_ID,RULE_NAME,ORDER_CODEFROM TB_RTA_CORE_CHECK_RULE WHERE FIND_IN_SET(RULE_ID, getChildLst(#ruleId#))ORDER BY PARENT_ID,ORDER_CODE

FIND_IN_SET函数语法介绍：

假如字符串str 在由N 子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。
一个字符串列表就是一个由一些被‘,’符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则   FIND_IN_SET() 函数被优化，使用比特计算。
如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。
mysql> SELECT FIND_IN_SET('b','a,b,c,d');

返回2因为b 在strlist集合中放在2的位置从1开始

select FIND_IN_SET('1','1'); 返回 就是1 这时候的strlist集合有点特殊 只有一个字符串 其实就是要求前一个字符串 一定要在后一个字符串集合中 才返回 大于0的数
select FIND_IN_SET('2','1，2');返回2
select FIND_IN_SET('6','1'); 返回0

注意：
select * from treenodes where FIND_IN_SET(id,'1,2,3,4,5');
使用find_in_set函数一次返回多条记录
id 是一个表的字段 然后每条记录分别是id等于1，2，3，4，5的时候
有点类似in （集合）
select * from treenodes where id in (1,2,3,4,5);

2.按字段删除重复记录

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people 
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)

3、查找表中多余的重复记录（多个字段） 
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

比方说在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select Name,Count(*) From A Group By Name Having Count(*) > 1

如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

实例1：按照BATCH_ID,FILE_NAME字段去重，保留CRT_DATE较小者

DELETE FROM tb_ctl_collect_log WHERE(BATCH_ID,FILE_NAME) IN (
 SELECT a.BATCH_ID,a.FILE_NAME FROM
  (
  SELECT BATCH_ID,FILE_NAME FROM tb_ctl_collect_log GROUP BYBATCH_ID,FILE_NAME HAVING COUNT(*)>1
  AND EXISTS (SELECT MIN(CRT_DATE) AS CRT_DATE FROM tb_ctl_collect_logGROUP BY BATCH_ID,FILE_NAME HAVING 
   COUNT(*)>1)
  )a
 );

3. You can't specify target table for update in FROMclause错误解决

delete from tbl where id in 
(
        select max(id) from tbl a where EXISTS
        (
            select 1 from tbl bwhere a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
)

改写成下面就行了：
delete from tbl where id in 
(
    select a.id from 
    (
        select max(id) id from tbl a where EXISTS
        (
            select 1 from tbl bwhere a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
    ) a
)

4 mysql数据导出命令(mysqldump )和导入命令(source )

导出TB_RTC_DIM_ORG_DEV至文件TB_ RTC _DIM_ORG_DEV中

-h 主机IP –u 数据库用户名 –p 数据库密码 storm 数据库

 mysqldump -h192.168.128.128 -uroot –p123456 storm TB_RTC_DIM_ORG_DEV>TB_RTC _DIM_ORG_DEV &

 导入TB_ RTC _DIM_ORG_DEV至另一个数据库中

source命令每次执行都会覆盖（若TB_RTC_DIM_ORG_DEV表存在）

 source /home/robinjun/data/ TB_ RTC _DIM_ORG_DEV

5 mysql shell模式导入导出数据命令

应用场景：INSERT...SELECT...和 CREATE TABLE...SELECT...语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。实际上，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，不推荐使用。

脚本示例：

#!/bin/bash
while true
do
//从表导出至文件
mysql –h192.168.128.128 -uroot –p123456 -N –e"SELECT * FROM storm.tb_rtc_data" >/data01/mysql01/data/ tb_rtc_data
//从文件导入至表
mysql -h192.168.128.128 -uroot -p123456  -N –e "load data infile '/data01/mysql01/data/tb_rtc_data '  into table storm.tb_rtc_dm_data_5_secfields terminated by '#' "
rm /data01/mysql01/data/tb_rtc_data
//休眠4s
sleep 4
done