MySQL 使用備忘

MySQL InnoDB 修復

同時使用 Group by 和 Order by 得到錯誤的結果

在 UserLog 資料表紀錄了使用者 id 以及網路 ip ,這是一個很常用的應用,如果要取出某 id 最後登入的 ip 位址,在同時使用 Group by Order by 之下就會得到錯誤的資料。

要注意的是,並不是錯誤的語法而是得到的資料我們想像中的資料,如果你有認真查驗過的話。

JOIN 的場合

直接 JOIN 倒序排序的資料即可

MariaDB 5.5 的腦殘 bug – 子查詢 ORDER BY 無效

上面這樣可以得到以 id 的倒序排序,這個沒問題,如果改成子查訊會如何?

你就會得到一個沒有排過序的資料 …

為什麼會說這個是 bug ,因為一來 MySQL 5.1 沒這個問題,二來在 MariaDB 官網提供了一個黑科技解決這個問題

那一串 LIMIT 不是黑科技?什麼才叫做黑科技 …

#1093 You can’t specify target table for update in FROM clause

MySQL 更新或刪除如果使用子查詢,遇到子查詢也使用到更新、刪除資料表時就會發生這個錯誤。

比較簡單的處理方式是,在子查詢使用 AS 規避資料表名稱

改成

更新案例

刪除案例

首先編輯挑選條件

將適合對象套用刪除,因為相同資料表也發生了 #1093 錯誤

建立跨資料庫外鍵

因為 phpMyAdmin 無法執行這個操作,只好自己手工輸入了…

 #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 位

UNION 的 ORDER BY,  LIMIT 及 offset

UNION 可將多條顯示相同欄位的 SQL 語句串連,建議將個別 SQL Command 使用 () 包住

UNION 的總 LIMIT 雖然可以套過下面語法實現,不過和想像中的分頁是不一樣的。

如果兩條 Query 都產生了 10 條結果,那最後的 LIMIT 就只會顯示前 10 筆

也可以對最後結果進行排序或其他判斷

子查詢

  • >=ANY 大于等于子查询中的某个值
  • <=ANY 小于等于子查询中的某个值
  • =ANY 等于子查询中的某个值
  • !=ANY或<>ANY 不等于子查询中的某个值
  • >ALL 大于子查询中的所有值
  • >=ALL 大于等于子查询中的所有值
  • <=ALL 小于等于子查询中的所有值
  • =ALL 等于子查询中的所有值
  • !=ALL或<>ALL 不等于子查询中的所有值

應用:留言回覆

從現有資料複製 INSERT INTO SELECT

MySQL 插入重複唯一值錯誤解決

#1194 – Table ‘tablename’ is marked as crashed and should be repaired

MyISAM 資料表毀損修復

參考:★用myisamchk修復損毀資料表 | ★羽毛貓のノート★

  1. /etc/rc.d/init.d/mysqld stop 停止資料庫
  2. myisamchk -f -r *.MYI
  3. /etc/rc.d/init.d/mysqld start

主要的關鍵是:必須先停止資料庫,才可以正常修復資料表

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 加入以下設定

  • 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

使用 PHP 程式設定

php://input 的陷阱

對於 php://input 的編碼只能藉由 my.cnf 的設定,php 尚未取得設定方法

顯示 MySQL 組態設定

— 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 指令

248 total views, no views today