» mysql の記事

先要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

如果要外來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