MySQL 常見問題

INTERVAL
http://rritw.com/a/shujuku/Oracle/20121122/258508.html
//一分钟前
select DATE_ADD(now(), Interval -1 minute)

怎麼樣搜尋所有資料表裡面是否含有命名為gid的欄位  在同一個db中
Ike:
show databases;
use information_schema;
show tables;
desc columns;
select table_name from columns where column_name ="gid";

mysql看欄位註解：
> desc table_name;
> show full columns from table_name;

查indexes設定
mysql> show indexes from [TABLE];

資料表名稱模糊查詢
http://stackoverflow.com/questions/10675987/show-tables-like-year-2010
> Show tables LIKE '%contest%'

group by 和order by時 order by 失效
http://blog.csdn.net/microji/article/details/3880975
SQL檔：
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `sex` varchar(255) NOT NULL,
  `test` varchar(255) NOT NULL,
  `dog_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `dog_id` (`dog_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

INSERT INTO `test` VALUES (1, 'dog1111', '1', 'test', 3);
INSERT INTO `test` VALUES (2, 'dog222', '3', '333', 3);
INSERT INTO `test` VALUES (3, 'cat1111', '2', '333', 1);
INSERT INTO `test` VALUES (4, 'cat2222', '1', 'testest', 1);

SQL > SELECT ma(angry) id ) AS max_id, test.*
FROM `test`
WHERE dog_id IN (1,3)
GROUP BY dog_id
ORDER BY id
這樣下order by 沒效
name要抓到dog2222和cat2222
結論：
好像都只能用子查詢
不關ORDER BY的關係
是GROUP BY 就已經找不到最大值的那一筆正確資料
解法：
SELECT test.*
FROM `test`,
(
 SELECT id AS m FROM `test` WHERE dog_id IN (1,3) GROUP BY dog_id
) AS `test_alias`
WHERE test.id = test_alias.m
ORDER BY id
=>子查詢要寫好(最好裡面只用id查)，不然會很慢 ( 善用 sub query 優化 order by limit )
=>不然就要查兩次了

因為這問題知道
GROUP BY 搭配的 GROUP_CONCAT() 功能

MS SQL 查詢欄位所屬資料表、資料庫
http://www.wretch.cc/blog/jhystart/14854084
[slave][cluster] search> SELECT TABLE_NAME, COLUMN_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'num_photos';

+------------------+-------------+------------------------+
| TABLE_NAME(table)| COLUMN_NAME | TABLE_SCHEMA(database) |
+------------------+-------------+------------------------+
| online_now       | num_photos  | cluster_logs           |

備份一個資料表
http://stackoverflow.com/questions/18741287/mysqldump-exports-only-one-table
$ mysqldump -u root -p mydb t1 t2 t3 > mydb_tables.sql

錯誤訊息：
MySQL ERROR 1045 (28000): Access denied for user 'root'@'x.x.x.x' (using password: YES)
可能是登錄的ip: x.x.x.x 沒有登錄的權限
解法：
1. 改表法(未試成功)
$ mysql -u root -p  #用root登錄mysql
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>FLUSH PRIVILEGES;

2. 授權法
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'mypassword' WITH GRANT OPTION;  #用 "%" 讓root從任何地方都能登錄
mysql>FLUSH PRIVILEGES;

查mysql資料儲存( datadir )位置
http://stackoverflow.com/questions/17968287/how-to-find-the-mysql-data-directory-from-command-line-in-windows
$ mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name LIKE "%dir"'
or
mysql> select @@datadir;

建立utf8編碼的資料庫
https://pario.no/2008/01/27/mysql-create-utf8-database/
mysql> CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;


http://stackoverflow.com/questions/11190668/format-number-to-2-decimal-places
select時無條件捨去
TRUNCATE(0.166, 2)
-- will be evaluated to 0.16
TRUNCATE(0.164, 2)
-- will be evaluated to 0.16
四捨五入
ROUND(0.166, 2)
-- will be evaluated to 0.17
ROUND(0.164, 2)
-- will be evaluated to 0.16

http://stackoverflow.com/questions/5951157/if-in-select-statement-choose-output-value-based-on-column-values
select時使用if else 
SELECT IF(type = 'P', amount, amount * -1) as amount FROM report
使用 if elseif else
SELECT CASE TYPE
           WHEN 'P' THEN amount
           WHEN 'N' THEN -amount
           ELSE 0
       END AS amount
FROM report

primary key 值歸零
mysql> ALTER table 資料表名稱 AUTO_INCREMENT = 1

報錯舊的欄位名稱
錯誤製造過程我忘了... 我把bank_card.car_number 改成bank_card.card_number後有一次報car_number的錯誤
原來是索引名稱的關係
1. 用phpmyadmin點表的結構
2. 點"索引"->"編輯" -> 原來是"索引名稱"是舊的car_number造成的

建立外鍵後，TRUNCATE tabe發生錯誤
http://stackoverflow.com/questions/5452760/truncate-foreign-key-constrained-table
#1701 - Cannot truncate a table referenced in a foreign key constraint (`bankdatabase`.`email`, CONSTRAINT `email_ibfk_1` FOREIGN KEY (`card_one`) REFERENCES `bankdatabase`.`bank_card` (`id`))
解法：
SET FOREIGN_KEY_CHECKS = 0;  -- Disable foreign key checking.

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;  -- Enable foreign key checking.

設定 Foreign key constraints 
http://jax-work-archive.blogspot.com/2007/10/innodb-mysql-foreign-key.html
phpmyadmin中，資料表"結構"，先設定"索引"(兩個表的字段都需要設)，然後點"關係查看
"這樣就可以設定該表的字段受外鍵約束時的行為
當關聯父資料表的主鍵紀錄行被刪除或修改時，InnoDB 對子資料表中紀錄行的處理方式：
CASCADE - 會將有所關聯的紀錄行也會進行刪除或修改。
SET NULL - 會將有所關聯的紀錄行設定成 NULL。
NO ACTION - 有存在的關聯紀錄行時，會禁止父資料表的刪除或修改動作。
RESTRICT - 與 NO ACTION 相同。

詳細的錯誤訊息可以在 MySQL 指令模式下輸入：
mysql> SHOW engine innodb status; 

產生視圖
http://www.codedata.com.tw/database/mysql-tutorial-11-views/
mysql> CREATE VIEW 名稱 AS 查詢敘述
ex.
mysql> CREATE VIEW test_view AS select * from users ....
用指令直接新增，要注意用戶有沒有訪問該view的權限
檢查：
phpmyadmin點該view的結構 -> 編輯視圖 ， 通常是 用户 出問題。ex. 把root@%改成 root@localhost（如果該db只允許本地連線）
或將該視圖導出 SQL

刪除視圖
mysql> DROP VIEW test_view;

在WHERE中不能使用SELECT AS的欄位為條件(Unknown Column In Where Clause)
http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error
ex.
mysql> SELECT u_name AS user_name FROM users WHERE user_name = "john";
Unknown Column 'user_name' in where clause
原因：
Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.
翻譯：
標準的SQL不允許使用欄位匿名在WHERE的子句中。這個限制實行因為在執行WHERE代碼時該列的值還沒確定

更新欄位值為NULL，當欄位值為零( 0 )時
MySQL: update a field only if condition is met
http://stackoverflow.com/questions/14893355/mysql-update-a-field-only-if-condition-is-met

UPDATE email
SET card_one= CASE
                  WHEN card_one=0 THEN NULL
                  ELSE card_one
              END,
    card_two= CASE
                  WHEN card_two=0 THEN NULL
                  ELSE card_two
              END

分拆欄位並排序
Splitting fields in sql query and sorting by them
http://stackoverflow.com/questions/9529198/splitting-fields-in-sql-query-and-sorting-by-them
Ex. 假設bank_card.name是這樣儲存的 ZZ-2 ABC-3 ABC-1 ，要照先英文再數字排列，
如ABC-1, ABC-3, ZZ-2

解：

SELECT *
FROM `bank_card`
ORDER BY SUBSTRING_INDEX(name,'-',1) ASC,
         SUBSTRING_INDEX(name,'-',-1)+0 ASC

結果：

求ABC-5的下一筆資料
http://stackoverflow.com/questions/3996498/mysql-fetch-previous-or-next-record-order-by-anyother-field-name-and-not-by-usin
解：

SELECT *
FROM `bank_card`
WHERE (SUBSTRING_INDEX(name,'-',1) > 'ABC'
       OR SUBSTRING_INDEX(name,'-',-1)+0 > '5')
ORDER BY SUBSTRING_INDEX(name,'-',1) ASC, SUBSTRING_INDEX(name,'-',-1)+0 ASC
LIMIT 1

結果：

說明：
因為name欄位特殊結構的關係，不能直接 name > 'ABC-5'，否則ABC-19會排序在ABC-5前面。要使用 (SUBSTRING_INDEX(name,'-',1) > 'ABC' OR SUBSTRING_INDEX(name,'-',-1)+0 > '5') 然後再 LIMIT 1




使用一個很大的資料庫時警告
mysql> use big_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
解法：
當初進MySQL時多加 -A 的參數
$ mysql -u root -p -A

看資料庫進程
mysql> show processlist ;

在group之後獲取最後的記錄 ( group by last value  )
http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group
SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

讓Windows 7 的MySQL可以從區網別台遠端登陸進來
http://pclevin.blogspot.com/2013/03/mysqlmysql.html
要從防火牆下手，自定義規則。連接埠 TCP 3306讓他允許連線

當下前三小時的時間
http://stackoverflow.com/questions/6020162/how-to-subtract-3-hours-from-a-datetime-using-sql
SELECT DATE_SUB(column, INTERVAL 3 HOUR) ...
前20秒的時間
INSERT INTO `table` (`txn_time`, `create_date`)
VALUES (
        DATE_SUB(NOW(), INTERVAL 20 SECOND), -- 20秒之前
        NOW() -- 當下時間
        );

遠端備份資料庫，且忽略AUTO_INCREMENT
http://stackoverflow.com/questions/15656463/mysqldump-export-structure-only-without-autoincrement
$ mysqldump --no-data -u root -p -h 192.168.x.x db_name | sed 's/ AUTO_INCREMENT=[0-9]*//g' > db_name.sql

遠端備份資料庫(僅資料)並還原
http://stackoverflow.com/questions/5387619/how-to-import-a-single-table-in-to-mysql-database-using-command-line
http://stackoverflow.com/questions/5109993/mysqldump-data-only
$ mysqldump --no-create-info -u username -p -h 192.168.x.1 databasename tableName > example.sql
只備份 tableName 表insert的資料，且不含schema ( --no-create-info )
$ mysql -u root -p -h 192.168.x.2 databasename < example.sql
不需要指定table名稱

遠端倒回資料時出現SUPER錯誤
http://stackoverflow.com/questions/11946012/how-to-add-super-privileges-to-mysql-database
$ mysql -u user -p -h 192.168.169.2 databasename < example.sql
Access denied; you need (at least one of) the SUPER privilege(s) for this operation
解法：
讓user有SUPER的權限
mysql> GRANT SUPER ON *.* TO 'user'@'192.168.169.%'
mysql> flush privileges;

left join 和 inner join的區別
http://oracled2k.pixnet.net/blog/post/21722078-sql-%E4%B8%89%E7%A8%AEjoin%E8%AA%9E%E6%B3%95%E7%9A%84%E5%B7%AE%E7%95%B0-
INNER JOIN
僅顯示兩資料表對應欄位中值相同的欄位。
LEFT JOIN
串聯兩個資料表中對應欄資料時，以資料表1的資料為主，若資料存在於資料表1，但資料表2沒有對應值時，仍顯示資料表1中的資料。
RIGHT JOIN
串聯兩個資料表中對應欄資料時，以資料表2的資料為主，若資料存在於資料表2，但資料表1沒有對應值時，仍顯示資料表2中的資料。

例：

country表

test表

INNER JOIN:
SELECT country.*, test.* FROM `country`, test WHERE country.id = test.id

LEFT JOIN:
SELECT country.*, test.* FROM `country` LEFT JOIN `test` on country.id = test.id

RIGHT JOIN:
SELECT country.*, test.* FROM `country` right JOIN `test` on country.id = test.id

結果等同於把country和test 對調後的 LEFT JOIN:
SELECT country.*, test.* FROM `test` LEFT JOIN `country` on country.id = test.id

結論：
LEFT JOIN和RIGHT JOIN會列出，以JOIN為主的表的所有資料，副表不存在的資料會顯示NULL


安全的刪除binlog
http://jjdai.zhupiter.com/2014/07/%E5%A6%82%E4%BD%95%E7%A7%BB%E9%99%A4%E4%B8%A6%E5%8F%96%E6%B6%88mysql%E7%9A%84binary-log%E6%AA%94%E6%A1%88/
因為一個12G的sql反覆倒入MySQL( 12G倒入VM實測花72分鐘 )，結果很多空間被binlog吃掉了。例如：倒入db前，機器硬碟空間有24G，因硬碟空間不足倒入失敗，然後drop掉db後 只剩20G，一查原來是binlog佔了很大的空間
將超過 x 天的 log “沖掉” (移除)
mysql> FLUSH LOGS;
看看狀態
mysql> SHOW MASTER STATUS;
指定刪除時間 '2013-08-16 21:30:00' 以前的 binary log 檔案
mysql> PURGE BINARY LOGS BEFORE '2013-08-16 21:30:00';

重設root密碼
http://emn178.pixnet.net/blog/post/87659567-mysql%E4%BF%AE%E6%94%B9%E5%AF%86%E7%A2%BC%E8%88%87%E5%BF%98%E8%A8%98%E5%AF%86%E7%A2%BC%E9%87%8D%E8%A8%AD
先停MySQL服務
# service mysql stop
# mysqld_safe --skip-grant-tables &
用上面方式啟動的MySQL，可以不用輸入密碼
# mysql -u root
然後再用SQL修改密碼
mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD("password") WHERE User='root';
mysql> flush privileges;
mysql> quit
最後重啟MySQL
# service mysql restart

報錯 You must SET PASSWORD before executing this statement
http://blog.sina.com.cn/s/blog_5f39af320102wbk1.html
明明使用密碼登入進去了，還需要密碼
mysql> create database db; 
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement 
mysql> SET PASSWORD = PASSWORD('123456'); 

找不到my.cnf
Rellik： 你沒設定 my.cnf 就不會有喔，全跑預設值。當然也不會寫 log

mysql console下不能輸入中文，但是bash console可以
解：
1. 在外面餵進去
$ mysql --database=db -e "show tables;"
或
2. 安裝phpmyadmin

PHP 遇到 Headers and client library minor version mismatch
https://blog.longwin.com.tw/2013/10/php-headers-client-library-minor-version-mismatch-2013/
http://stackoverflow.com/questions/13159518/how-to-enable-mysqlnd-for-php
刷php網頁時出現警告：
mysqli::connect(): Headers and client library minor version mismatch. centos
原因：
Client library 與 Headers 的版本不同
# php -i | grep Client
Client API version => 5.6.28-76.1
Client API library version => 5.6.28-76.1
Client API header version => 5.5.47-MariaDB
Client API version => 5.6.28-76.1
解法：
移除php-mysql改安裝
# yum remove php-mysql
# yum install php-mysqlnd
最後記得重啟php-fpm
# service php-fpm restart
檢查：
# php -i | grep Client
Client API version => mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $
Client API library version => mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $
Client API version => mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $

列出DB中所有table的大小並排序
Mysql list tables and sizes - order by size
https://stackoverflow.com/questions/14569940/mysql-list-tables-and-sizes-order-by-size

SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

快速清空table資料
https://stackoverflow.com/questions/17762361/truncate-table-taking-very-long-time-is-it-normal
如果在表資料很大的時候，truncate效果不是很好，可以使用以下方法

RENAME TABLE tablename TO tablename_backup;

CREATE TABLE tablename LIKE tablename_backup;

DROP TABLE tablename_backup;

s
看該表生成的SQL
https://stackoverflow.com/questions/11739014/how-to-generate-a-create-table-script-for-an-existing-table-in-phpmyadmin
SHOW CREATE TABLE tablename
連產生表分區Partition的內容都能看到

開表開庫時的character encoding和collation 要選什麼？
https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
utf8mb4 和 utf8mb4_unicode_ci
There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.
幾乎可以確定（almost certainly）沒有理由再使用 utf8mb4_general_ci ，因為現在的CPU已經很快了。你的資料庫幾乎肯定會受到其他瓶頸限制。

新增PARTITION（ PRIMARY KEY (`id`,`created_at`) ）
mysql> ALTER TABLE transactions PARTITION BY LIST (to_days(created_at)) ( partition p20190519 VALUES IN (to_days('2019-05-19')));
移除PARTITION
mysql> ALTER TABLE transactions REMOVE PARTITIONING;
換PARTITION字段
mysql> ALTER TABLE `transactions` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `open_at`) USING BTREE;
mysql> ALTER TABLE transactions PARTITION BY LIST (to_days(open_at)) ( partition p20190519 VALUES IN (to_days('2019-05-19')));

Incorrect integer value: '' for column 錯誤

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'xx' at row 1

https://stackoverflow.com/a/53154716  Incorrect integer value: '' for column 'id' at row 1

檢查 sql_mode 

> SELECT @@sql_mode;

應該是 STRICT_TRANS_TABLES 在作怪 

解析mysqlbinlog

# mysqlbinlog  --base64-output=decode-rows -v --start-datetime="2018-06-03 19:00:00"  --stop-datetime="2018-06-03 19:42:00" mysql-bin.000051 > 20180603190000_20180603194200-mysql-bin.000051.sql

--base64-output=decode-rows -v => 避免看到亂碼

--start-datetime="2018-06-03 19:00:00"  --stop-datetime="2018-06-03 19:42:00" => 只看2018-06-03 19:00:00到2018-06-03 19:42:00的binlog

mysql-bin.000051 => 指定binlog檔案

> 20180603190000_20180603194200-mysql-bin.000051.sql => 輸出SQL文件，用vim看syntax high light

欄位要選NULL還是NOT NULL

https://stackoverflow.com/questions/1736096/mysql-null-or-not-null-that-is-the-question  MySQL NULL or NOT NULL That is The Question?

Sparky：

NULL means you do not have to provide a value for the field...

NULL 表示您不必為該字段提供值...  => 如用戶系統的user_id

NOT NULL means you must provide a value for the fields.

NOT NULL表示您必須為字段提供一個值 => 如用戶系統的配偶名字段

MarkR：

I would suggest

我建議

• Use NOT NULL on every field if you can
• 如果可以，請在每個字段上使用NOT NULL
• Use NULL if there is a sensible reason it can be null
• 如果有合理的原因，可以使用NULL

OMG Ponies：

That is determined by your business rules.

這取決於您的業務規則

Generally you want as many columns as possible to be NOT NULL because you want to be sure data is always there.

通常你希望盡可能多的字段是 NOT NULL ，因為你要確保資料始終存在

https://stackoverflow.com/a/230923  NULL in MySQL (Performance & Storage)

MyISAM 中NULL 不會減少儲存空間