不來嗯(咻～)的技術筆記

FK設的太複雜想刪刪不掉沒有設定ON DELETE CASCADE時，可查詢目前誰使用我當作FK

USE information_schema;
SELECT * FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'table_name'
  AND REFERENCED_COLUMN_NAME = 'column_name';

有大量流水號的表格，如Log001, Log002, Log003, Log004.....一次性刪除

SET group_concat_max_len = 1024 * 1024 * 10;

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';') INTO @dropcmd FROM 
  information_schema.tables 
WHERE 
 table_schema='schema_name' 
  AND table_name LIKE 'Log%';

PREPARE str FROM @dropcmd; EXECUTE str; DEALLOCATE PREPARE str;

查詢互設關注的兩人( sourceID發起者, targetID被關注者, x查詢人)，WHERE IN or NOT IN只能接受單一結果的子句

SELECT * FROM 
 tbl_follow
WHERE targetID IN 
 (SELECT sourceID FROM tbl_follow WHERE targetID = x)
  AND sourceID = x;