一天一点点(20151123)

20151123
主题：msyql批处理脚本实例
（来自MySQL reference manual 3.5）
=============================================
#!/bin/sh
mysql --user='root' --password='mysql123' -h 120.26.81.89 --database='qwifi'>>qwifi.out< SET @start=UNIX_TIMESTAMP();
select * from page_access_record;
SET
@s=@seconds:=90000+UNIX_TIMESTAMP()-@start,
@d=TRUNCATE(@s/86400,0), @s=MOD(@s,86400),
@h=TRUNCATE(@s/3600,0), @s=MOD(@s,3600),
@m=TRUNCATE(@s/60,0), @s=MOD(@s,60),
@day=IF(@d>0,CONCAT(@d,' day'),''),
@hour=IF(@d+@h>0,CONCAT(IF(@d>0,LPAD(@h,2,'0'),@h),' hour'),''),
@min=IF(@d+@h+@m>0,CONCAT(IF(@d+@h>0,LPAD(@m,2,'0'),@m),' min.'),''),
@sec=CONCAT(IF(@d+@h+@m>0,LPAD(@s,2,'0'),@s),' sec.');

SELECT
CONCAT(@seconds,' sec.') AS seconds,
CONCAT_WS(' ',@day,@hour,@min,@sec) AS elapsed;
quit
!!

A more secure way to use the shell.
So that passwords are not embedded in the shell source file create a password file:

echo "batchpassword" > /etc/security/mysqlpassword
chmod 200 /etc/security/mysqlpassword

Then in your script:

echo "update tablex set x=1 where a=2;" | mysql mydb --user=batchdb --password=`cat /etc/security/mysqlpassword`

This assumes you have created a user called "batchdb" with that password and the correct access rights to the database called "mydb".

-- 文档中还有很多用法

主题：去重统计
（来自MySQL reference manual 3.6.8）
=============================================
SELECT
  YEAR,
  MONTH,
  BIT_COUNT(BIT_OR(1 << DAY)) AS days
FROM
  t1
GROUP BY YEAR,
  MONTH ;