使用 .sql 匯入資料庫
|
# mysql -u username -p database_name < file.sql |
—
使用 phpMyAdmin 匯入有 foreign key 的資料表處理方案
匯入 fk 資料表時,就算所有關聯資料表一起匯入也會因為順序不一致而發生
|
Cannot add or update a child row: a foreign key constraint fails |
這個錯誤
從網站上可以查詢到必須設定 SET FOREIGN_KEY_CHECKS=0; ,但是使用 SQL 指令執行後再使用 SELECT @@FOREIGN_KEY_CHECKS; 查看會發現還是 1 ,也就是沒有被關閉
解決的方法是修改匯入 .sql 檔案,在最開頭加上 SET FOREIGN_KEY_CHECKS=0; 以及結尾加上 SET FOREIGN_KEY_CHECKS=1;
類似像這樣
|
SET FOREIGN_KEY_CHECKS=0; -- -- 原來的 SQL 指令 -- SET FOREIGN_KEY_CHECKS=1; |
—
MySQL InnoDB 修復
—
同時使用 Group by 和 Order by 得到錯誤的結果
|
SELECT Users_id, ipv4 FROM (select id,users_id,ipv4 from UserLog order by id desc) as temp group by users_id order by id desc; |
在 UserLog 資料表紀錄了使用者 id 以及網路 ip ,這是一個很常用的應用,如果要取出某 id 最後登入的 ip 位址,在同時使用 Group by Order by 之下就會得到錯誤的資料。
要注意的是,並不是錯誤的語法而是得到的資料我們想像中的資料,如果你有認真查驗過的話。
JOIN 的場合
直接 JOIN 倒序排序的資料即可
|
select Users.id, ipv4 from Users JOIN (select id,Users_id,ipv4 from UserLog order by id desc) `UserLog` ON Users.id=UserLog.Users_id |
MariaDB 5.5 的腦殘 bug – 子查詢 ORDER BY 無效
|
SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC |
上面這樣可以得到以 id 的倒序排序,這個沒問題,如果改成子查訊會如何?
|
select * from (SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC) `a` |
你就會得到一個沒有排過序的資料 …
為什麼會說這個是 bug ,因為一來 MySQL 5.1 沒這個問題,二來在 MariaDB 官網提供了一個黑科技解決這個問題
|
select * from (SELECT * FROM `UserLog` WHERE `Users_id` = 565 ORDER BY `UserLog`.`id` DESC LIMIT 18446744073709551615) `a` |
那一串 LIMIT 不是黑科技?什麼才叫做黑科技 …
—
#1093 You can’t specify target table for update in FROM clause
MySQL 更新或刪除如果使用子查詢,遇到子查詢也使用到更新、刪除資料表時就會發生這個錯誤。
比較簡單的處理方式是,在子查詢使用 AS 規避資料表名稱
改成
|
SELECT * FROM ( SELECT * FROM AAA) AS AliasAAA |
更新案例
|
UPDATE Booking SET ClassPoint =0 WHERE id = ( SELECT id FROM ( SELECT id FROM Booking WHERE Classroom_id = '123' ) AS B ) |
刪除案例
首先編輯挑選條件
|
SELECT Users.id FROM Users JOIN UserLog ON Users.id=UserLog.Users_id WHERE UserIdentity_Code='Free' AND IPv4='211.20.170.215' AND DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d') >= DATE_FORMAT( UserLog.Create_Time, '%Y%m%d') GROUP BY Users.id |
將適合對象套用刪除,因為相同資料表也發生了 #1093 錯誤
|
DELETE FROM Users WHERE id IN ( SELECT AliasUsers.id FROM ( SELECT * FROM Users) AS AliasUsers JOIN UserLog ON AliasUsers.id=UserLog.Users_id WHERE UserIdentity_Code='Free' AND IPv4='211.20.170.215' AND DATE_FORMAT( DATE_SUB(NOW(), INTERVAL 7 DAY), '%Y%m%d') >= DATE_FORMAT( UserLog.Create_Time, '%Y%m%d') GROUP BY AliasUsers.id ) |
—
建立跨資料庫外鍵
因為 phpMyAdmin 無法執行這個操作,只好自己手工輸入了…
|
ALTER TABLE `Albums`.`Supervisor` ADD FOREIGN KEY ( `Member_id` ) REFERENCES `Member`.`Member` ( `id` ) ON DELETE CASCADE ON UPDATE CASCADE ; |
#1005 – Can’t create table
Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
如果外鍵關聯動作設定為 SET NULL 就有可能會出現這個錯誤,改為其他動作試試
—
新增 FOREIGN KEY 發生 #1452 – Cannot add or update a child row: a foreign key constraint fails 錯誤
參考:
使用 Foreign Key 關鍵
- 主鍵必須為索引
- 外部鍵必須為 Primary Key
- 動作請設定為 CASCADE 讓關聯資料隨著刪除、修改一併更動
- 如果發生 #1452 錯誤,應該是資料表有對應錯誤的資料,必須要正確才能順利建立或修改
—
MySQL 的日期、時間切割計算
資料表有一個 time 欄位,型態為 timestamp
需求:顯示小時區間以及該小時區間的數值 Data 平均值,平均值取小數點 0 位
|
SELECT DATE_FORMAT(time, '%Y/%m/%d %H') D, FORMAT(AVG(`Data`),0) as T FROM `Table` GROUP BY MONTH(time),DAY(time),HOUR(time) |
—
UNION 的 ORDER BY, LIMIT 及 offset
UNION 可將多條顯示相同欄位的 SQL 語句串連,建議將個別 SQL Command 使用 () 包住
|
( SELECT * FROM T1 LIMIT 0, 10 ) UNION ( SELECT * FROM T2 LIMIT 0, 10 ) |
UNION 的總 LIMIT 雖然可以套過下面語法實現,不過和想像中的分頁是不一樣的。
如果兩條 Query 都產生了 10 條結果,那最後的 LIMIT 就只會顯示前 10 筆
|
( SELECT * FROM T1 LIMIT 0, 10 ) UNION ( SELECT * FROM T2 LIMIT 0, 10 ) LIMIT 0, 10 |
也可以對最後結果進行排序或其他判斷
|
( SELECT * FROM T1 LIMIT 0, 10 ) UNION ( SELECT * FROM T2 LIMIT 0, 10 ) ORDER BY Column DESC |
—
子查詢
- >=ANY 大于等于子查询中的某个值
- <=ANY 小于等于子查询中的某个值
- =ANY 等于子查询中的某个值
- !=ANY或<>ANY 不等于子查询中的某个值
- >ALL 大于子查询中的所有值
- >=ALL 大于等于子查询中的所有值
- <=ALL 小于等于子查询中的所有值
- =ALL 等于子查询中的所有值
- !=ALL或<>ALL 不等于子查询中的所有值
應用:留言回覆
|
SELECT * FROM 訊息 WHERE sid=ANY ( SELECT 回覆sid FROM 訊息 WHERE 回覆sid<>'' ) ORDER BY sid DESC LIMIT 0, 1 |
—
從現有資料複製 INSERT INTO SELECT
|
INSERT INTO `Media_File` ( create_date,source_filename,create_user,media_filename,來源媒體資訊,產品連結) SELECT '20131218100600', source_filename,create_user,media_filename,來源媒體資訊,'166' FROM `Media_File` WHERE sid=110724 |
—
MySQL 插入重複唯一值錯誤解決
|
INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '9999'); |
—
#1194 – Table ‘tablename’ is marked as crashed and should be repaired
MyISAM 資料表毀損修復
參考:★用myisamchk修復損毀資料表 | ★羽毛貓のノート★
- /etc/rc.d/init.d/mysqld stop 停止資料庫
- myisamchk -f -r *.MYI
- /etc/rc.d/init.d/mysqld start
主要的關鍵是:必須先停止資料庫,才可以正常修復資料表
—
開啟 log
開啟 general-log 功能可以紀錄 MySQL 所有執行的 SQL 指令,
|
show variables like 'log'; |
編輯 my.cnf
|
[mysqld] general-log = 1 # 打開 General Log log_output=TABLE # 將 Log 儲存在 mysql 資料庫 內的 general_log 資料表內 |
使用 SQL 指令修改 Log 狀態
|
SET GLOBAL general_log = 'ON'; |
—
mysqldump UTF-8 資料庫卻不是 UTF-8 編碼檔案?
如果原資料庫或資料表的排序規則是 utf8_general_ci 的話,那修改成 utf8_unicode_ci 之後再測試看看。
—
關閉 log-bin
mysql-bin.000001文件的來源及處理方法 – PHP+MySQL – 程式設計 – 頂客論壇 – 台灣forum,Taiwan論壇bbs
執行以下 SQL 指令,清除檔案
註解 my.cnf 內的 log-bin ,重新啟動 MySQL Server
—
修改語系為 UTF8
編輯 /etc/my.cnf 加入以下設定
|
[mysqld] init-connect = 'SET NAMES utf8' character-set-server = utf8 [client] default-character-set=utf8 |
- init-connect=’SET NAMES utf8′
character_set_system | utf8
- character-set-server = utf8
character_set_server | utf8
character_set_database | utf8
- default-character-set=utf8
character_set_client | utf8
character_set_connection | utf8
character_set_results | utf8
|
mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ |
使用 PHP 程式設定
|
define( "PDO_DSN", "mysql:dbname=". MYSQL_DBNAME .";host=". MYSQL_HOST.";charset=utf8" ); $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); $PDO = new PDO(PDO_DSN, MYSQL_USERNAME, MYSQL_PASSWORD, $options); $PDO->exec("set character_set_server=utf8;"); |
php://input 的陷阱
對於 php://input 的編碼只能藉由 my.cnf 的設定,php 尚未取得設定方法
—
顯示 MySQL 組態設定
|
show variables like 'collation%'; |
— Get the current value that could be already modified by SET sql_mode statement
— executed in the current session
SELECT @@sql_mode;
— Get the global value, not affected by SET sql_mode
SELECT @@GLOBAL.sql_mode;
—
將 SQL mode 移除預設嚴格模式
修改 my.ini
或使用 SQL 指令
|
SET GLOBAL sql_mode = ''; |
—