MySql避免重复插入记录

方案一:使用ignore关键字

如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用:

insert ignore into table_name(email,phone,user_id) values('test9@163.com','99999','9999'),这样当有重复记

录就会忽略,执行后返回数字0,还有个应用就是复制表,避免重复记录:

insert ignore into table(name) select name from table2

方案二:使用Replace

replace的语法格式为:

1. replace into table_name(col_name, ...) values(...)

2. replace into table_name(col_name, ...) select ...

3. replace into table_name set col_name=value, ...

Continue reading...

MySQL使用instr函数模糊查询

一般SQL中模糊查询使用like,今天看到一个MySQL使用instr函数模糊查询的方法:

查询table表中用户名包含Cyrec的记录:

SELECT * FROM table WHERE 1 = instr(`username`,'Cyrec');

等价于SELECT * FROM table WHERE `username` like '%Cyrec%';

(PS:instr这个MySQL函数返回username字段中包含Cyrec的位置,如果没找到返回0;)

使用like类似‘%xxx%’ 双向模糊匹配是无法使用索引的,要进行全表扫描('xxx%'这样满足最左前缀匹配可以使用索

引效率很高).网上说有大量记录的时候instr函数效率比用like('%xxx%')高,于是自己在本地试了下,执行时间差不

多用explain看到两种方式都是进行全表扫描(type=ALL),预计扫描的rows也一样,然后在status中看到执行计划预

测的last_query_cost也是一样。不知道到底哪个好,以后遇到模糊查询的时间再试下,不过这条SQL语句的思路

挺好的。

Continue reading...

刚哥写的牛B查询SQL语句。

  1. set @tmp_date = '2012-02-22';   
  2. #DATE_ADD(@tmp_date,INTERVAL 1 DAY)   
  3. SELECT  a.username  帐号,   
  4.                 a.tel 电话,   
  5.                 a.qq    QQ,   
  6.                 IFNULL(day1.money,'-') '2012-02-29(差额)',   
  7.                 IFNULL(day2.money,'-') '2012-03-01(差额)',   
  8.                 IFNULL(day3.money,'-') '2012-03-02(差额)',   
  9.                 IFNULL(day4.money,'-') '2012-03-03(差额)',   
  10.                 IFNULL(day5.money,'-') '2012-03-04(差额)',   
  11.                 IFNULL(day6.money,'-') '2012-03-05(差额)',   
  12.                 IFNULL(day7.money,'-') '2012-03-06(差额)',   
  13.                 IFNULL(day1.bitt,'-') '2012-02-29(百分比)',   
  14.                 IFNULL(day2.bitt,'-') '2012-03-01(百分比)',   
  15.                 IFNULL(day3.bitt,'-') '2012-03-02(百分比)',   
  16.                 IFNULL(day4.bitt,'-') '2012-03-03(百分比)',   
  17.                 IFNULL(day5.bitt,'-') '2012-03-04(百分比)',   
  18.                 IFNULL(day6.bitt,'-') '2012-03-05(百分比)',   
  19.                 IFNULL(day7.bitt,'-') '2012-03-06(百分比)'   
  20. from (   
  21.         select  tmp1.uid,   
  22. #                       tmp1.`date` date1,   
  23. #                       tmp2.`date` date2,   
  24. #                       tmp1.nhome nhome1,   
  25. #                       tmp2.nhome nhome2,   
  26.                         (tmp1.nhome - tmp2.nhome) money,   
  27.                         (tmp1.nhome - tmp2.nhome)/tmp1.nhome bitt   
  28.         from (   
  29.                 select  uid,   
  30.                                 `date`,   
  31.                                 sum(nhome) nhome   
  32.                 from swcms_income   
  33.                 where `date` = @tmp_date   
  34.                 GROUP BY uid,`date`   
  35.         ) as tmp1   
  36.         inner join (   
  37.                 select  uid,   
  38.                                 `date`,   
  39.                                 sum(nhome) nhome   
  40.                 from swcms_income   
  41.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 7 DAY)   
  42.                 GROUP BY uid,`date`) as tmp2   
  43.         on tmp1.uid = tmp2.uid   
  44.                 and DATEDIFF(tmp2.`date`,tmp1.`date`) = 7   
  45.                 and tmp1.nhome - tmp2.nhome > 0   
  46.                 and (tmp1.nhome - tmp2.nhome)/tmp1.nhome >= 0.1) as day1   
  47.         LEFT OUTER JOIN    
  48.         (   
  49.         select  tmp1.uid,   
  50.                         (tmp1.nhome - tmp2.nhome) money,   
  51.                         (tmp1.nhome - tmp2.nhome)/tmp1.nhome bitt   
  52.         from (   
  53.                 select  uid,   
  54.                                 `date`,   
  55.                                 sum(nhome) nhome   
  56.                 from swcms_income   
  57.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 1 DAY)   
  58.                 GROUP BY uid,`date`   
  59.         ) as tmp1   
  60.         inner join (   
  61.                 select  uid,   
  62.                                 `date`,   
  63.                                 sum(nhome) nhome   
  64.                 from swcms_income   
  65.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 7+1 DAY)   
  66.                 GROUP BY uid,`date`) as tmp2   
  67.         on tmp1.uid = tmp2.uid   
  68.                 and DATEDIFF(tmp2.`date`,tmp1.`date`) = 7   
  69.                 and tmp1.nhome - tmp2.nhome > 0   
  70.                 and (tmp1.nhome - tmp2.nhome)/tmp1.nhome >= 0.1) as day2   
  71.     ON day1.uid = day2.uid   
  72.     LEFT OUTER JOIN  
  73. (   
  74.         select  tmp1.uid,   
  75.                         (tmp1.nhome - tmp2.nhome) money,   
  76.                         (tmp1.nhome - tmp2.nhome)/tmp1.nhome bitt   
  77.         from (   
  78.                 select  uid,   
  79.                                 `date`,   
  80.                                 sum(nhome) nhome   
  81.                 from swcms_income   
  82.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 2 DAY)   
  83.                 GROUP BY uid,`date`   
  84.         ) as tmp1   
  85.         inner join (   
  86.                 select  uid,   
  87.                                 `date`,   
  88.                                 sum(nhome) nhome   
  89.                 from swcms_income   
  90.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 7+2 DAY)   
  91.                 GROUP BY uid,`date`) as tmp2   
  92.         on tmp1.uid = tmp2.uid   
  93.                 and DATEDIFF(tmp2.`date`,tmp1.`date`) = 7   
  94.                 and tmp1.nhome - tmp2.nhome > 0   
  95.                 and (tmp1.nhome - tmp2.nhome)/tmp1.nhome >= 0.1) as day3   
  96.         ON day2.uid = day3.uid   
  97.         LEFT OUTER JOIN  
  98.         (   
  99.         select  tmp1.uid,   
  100.                         (tmp1.nhome - tmp2.nhome) money,   
  101.                         (tmp1.nhome - tmp2.nhome)/tmp1.nhome bitt   
  102.         from (   
  103.                 select  uid,   
  104.                                 `date`,   
  105.                                 sum(nhome) nhome   
  106.                 from swcms_income   
  107.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 3 DAY)   
  108.                 GROUP BY uid,`date`   
  109.         ) as tmp1   
  110.         inner join (   
  111.                 select  uid,   
  112.                                 `date`,   
  113.                                 sum(nhome) nhome   
  114.                 from swcms_income   
  115.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 7+3 DAY)   
  116.                 GROUP BY uid,`date`) as tmp2   
  117.         on tmp1.uid = tmp2.uid   
  118.                 and DATEDIFF(tmp2.`date`,tmp1.`date`) = 7   
  119.                 and tmp1.nhome - tmp2.nhome > 0   
  120.                 and (tmp1.nhome - tmp2.nhome)/tmp1.nhome >= 0.1) as day4   
  121.         ON day3.uid = day4.uid   
  122.         LEFT OUTER JOIN  
  123.         (   
  124.         select  tmp1.uid,   
  125.                         (tmp1.nhome - tmp2.nhome) money,   
  126.                         (tmp1.nhome - tmp2.nhome)/tmp1.nhome bitt   
  127.         from (   
  128.                 select  uid,   
  129.                                 `date`,   
  130.                                 sum(nhome) nhome   
  131.                 from swcms_income   
  132.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 4 DAY)   
  133.                 GROUP BY uid,`date`   
  134.         ) as tmp1   
  135.         inner join (   
  136.                 select  uid,   
  137.                                 `date`,   
  138.                                 sum(nhome) nhome   
  139.                 from swcms_income   
  140.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 7+4 DAY)   
  141.                 GROUP BY uid,`date`) as tmp2   
  142.         on tmp1.uid = tmp2.uid   
  143.                 and DATEDIFF(tmp2.`date`,tmp1.`date`) = 7   
  144.                 and tmp1.nhome - tmp2.nhome > 0   
  145.                 and (tmp1.nhome - tmp2.nhome)/tmp1.nhome >= 0.1) as day5   
  146.         ON day4.uid = day5.uid   
  147.         LEFT OUTER JOIN  
  148.         (   
  149.         select  tmp1.uid,   
  150.                         (tmp1.nhome - tmp2.nhome) money,   
  151.                         (tmp1.nhome - tmp2.nhome)/tmp1.nhome bitt   
  152.         from (   
  153.                 select  uid,   
  154.                                 `date`,   
  155.                                 sum(nhome) nhome   
  156.                 from swcms_income   
  157.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 5 DAY)   
  158.                 GROUP BY uid,`date`   
  159.         ) as tmp1   
  160.         inner join (   
  161.                 select  uid,   
  162.                                 `date`,   
  163.                                 sum(nhome) nhome   
  164.                 from swcms_income   
  165.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 7+5 DAY)   
  166.                 GROUP BY uid,`date`) as tmp2   
  167.         on tmp1.uid = tmp2.uid   
  168.                 and DATEDIFF(tmp2.`date`,tmp1.`date`) = 7   
  169.                 and tmp1.nhome - tmp2.nhome > 0   
  170.                 and (tmp1.nhome - tmp2.nhome)/tmp1.nhome >= 0.1) as day6   
  171.         ON day5.uid = day6.uid   
  172.         LEFT OUTER JOIN  
  173.         (   
  174.         select  tmp1.uid,   
  175.                         (tmp1.nhome - tmp2.nhome) money,   
  176.                         (tmp1.nhome - tmp2.nhome)/tmp1.nhome bitt   
  177.         from (   
  178.                 select  uid,   
  179.                                 `date`,   
  180.                                 sum(nhome) nhome   
  181.                 from swcms_income   
  182.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 6 DAY)   
  183.                 GROUP BY uid,`date`   
  184.         ) as tmp1   
  185.         inner join (   
  186.                 select  uid,   
  187.                                 `date`,   
  188.                                 sum(nhome) nhome   
  189.                 from swcms_income   
  190.                 where `date` = DATE_ADD(@tmp_date,INTERVAL 7+6 DAY)   
  191.                 GROUP BY uid,`date`) as tmp2   
  192.         on tmp1.uid = tmp2.uid   
  193.                 and DATEDIFF(tmp2.`date`,tmp1.`date`) = 7   
  194.                 and tmp1.nhome - tmp2.nhome > 0   
  195.                 and (tmp1.nhome - tmp2.nhome)/tmp1.nhome >= 0.1) as day7   
  196.         ON day6.uid = day7.uid   
  197.         INNER JOIN swcms_member a   
  198.         on day1.uid  = a.uid  

Continue reading...

看到三条经典的SQL语句。

sql.jpg

Continue reading...

SQL UPDATE 不存在则插入,存在则更新该记录。

  1. INSERT `sum` (`uid`,`pid`,`sum`,`date`) values (1,0,0,'2012-01-01') ON DUPLICATE KEY UPDATE `sum`=`sum`+1,`pid`=1  

注意,要使用这条语句,前提条件是这个表必须有一个唯一索引或主键。
建表的时候,千万别忘记了给做一个UNIQUE。

Continue reading...



about me

  • 陶之11's Blog Name:陶之11 OICQ:14779023 Site:Pao11.com

分类

快捷入口