» mysql の記事

SELECT `accountname` FROM `tablename` 
where `log_date`>'2012-12-30 00:00:00' 
and `log_date`<'2013-01-03 23:59:59' 
group by `log_date`,`accountname` 
having COUNT(DISTINCT date(log_date));

先要create function

DELIMITER //
CREATE FUNCTION bits_find_N1(bits BIGINT, trait BIGINT)
  RETURNS BOOL
  BEGIN
    WHILE bits <> 0 DO
      IF ((bits & trait) = trait) THEN
        RETURN TRUE;
      END IF;
      SET bits = bits >> 1;
    END WHILE;
    RETURN FALSE;
  END//
  DELIMITER ;

mysql statement

SELECT accountname AS bit FROM tablename
WHERE log_date BETWEEN '2012-12-30 00:00:00' AND '2013-01-03 23:59:59'
GROUP BY uid
HAVING bits_find_N1(BIT_OR(1 << datediff(log_date, '2012-12-30')),
b'11111') IS TRUE;

如果某column 數目要到一定數目

SELECT accountname AS bit, sum(cardamount) FROM tablename
WHERE log_date BETWEEN '2012-12-30 00:00:00' AND '2013-01-03 23:59:59'
GROUP BY uid
HAVING bits_find_N1(BIT_OR(1 << datediff(log_date, '2012-12-30')),
b'11111') IS TRUE and sum(cardamount)>=1200;

詳細可參考

http://www.mysqlops.com/2012/03/06/an_interesting_query.html

Mac OS X Server Apache 無法存取本機 MySQL Server 解決方式

由 phpinfo 中得知，是將 Mac 中的 PHP 是將 mysql.sock 放在 /var/mysql 下，

而 Mac 中的 MySQL 是放在 /tmp/mysql.sock。

所以要由本機的 Web Server 利用 PHP 連接本機的 MySQL 就會一直出現無法連線的錯誤。

利用底下的指令可以解決這個問題

sudo mkdir /var/mysql
sudo chown mysql /var/mysql
sudo ln -s /tmp/mysql.sock /var/mysql/

如果要外來ip connect 去mysql
要去更改my.cnf

ubuntu 位置為

/etc/mysql/my.cnf

將bind-address 指向要connect 去mysql 的ip

bind-address   = 127.0.0.111

PS: bind-address 不能多於一個，如果多於一個的情況就需要comment 左skip-external-locking 同bind-address,再用firewall block