MySQL 快速匯入 CSV 檔案 – 使用 LOAD DATA

直接匯入 CSV 檔案到 MySQL 資料庫

實際範例

需要注意指令順序

效能

如果可以使用 LOAD DATA 一定會比使用程式拆解後逐筆插入快,五萬筆的匯入在小白 MacBook 上也只需不到 3 秒

ERROR 1045 (28000): Access denied for user ‘XXXXX’@’%’ (using password: YES)

將 LOAD DATA INFILE 改成 LOAD DATA LOCAL INFILE

MySQL Partitioning 分區

MySQL 分區可以設定更改原先資料儲存結果。

以 MYISAM 引擎為例,原本一個 A 資料表在硬碟會存在

  • A.frm
  • A.MYD
  • A.MYI

三個檔案,無論資料筆數多寡都放在同一檔案內,這時資料儲存限制 == 作業系統單一檔案大小限制。在目前主流檔案系統之下,單一檔案大小限制在 4G ~ 16TByte 之間。

資料表太大對資料庫不是好事,有可能只為了讀取 10Byte 的資料,卻將整個資料表都讀取一遍,如果可能還是需要控制資料表的實體儲存容量,達到增進效能以及方便管理的目的。

參考

 

MySQL_Partitioning01

以時間作為分區依據

以一個有 AUTO_INCREMENT 及 CURRENT_TIMESTAMP 的資料表為例,

關鍵是「必須把區分時間的欄位包含到 Primary Key 內

 

一個將無分區資料表修改為具分區功能範例

  1. 建立一個具有分區功能資料表,必須和原資料表資料結構相同,準備做替換用
  2. 將原資料表資料複製到此分區資料表,使用的是 INSERT SELECT 語法
  3. 刪除原資料表
  4. 將分區資料表名稱更換為原資料表名稱

 

顯示資料表分區資訊

使用的是顯示建立資料表資訊技巧

一個分區資料表建立後修改範例

 

 

分區區分到年、月

順便說一個情況,第一個設定是 199501 ,代表 1995年1月以前的資料存在此,而199502 則只存放 1995年 2 月的資料,因為沒有 MAXVALUE 所以超過的日期會新增失敗

InnoDB 如何分區區分檔案

重新啟動資料庫

PDO 長連接 PDO::ATTR_PERSISTENT 測試及了解

PHP PDO MySQL 長連結設定及影響

 

設定短連接,一次資料庫查詢程式

短連接,一次資料庫查詢結果

設定為長連接 PDO::ATTR_PERSISTENT => true

長連接,一次資料庫查詢結果

短連接,連續多查詢程式

短連接,連續多查詢結果

長連接,連續多查詢程式

長連接,連續多查詢結果

 

MySQL SELECT REGEXP, PHP 刪除空白陣列

MySQL REGEXP

參考至: MySQL IN with LIKE

 

PHP Array()

MySQL 使用備忘

使用 .sql 匯入資料庫

使用 phpMyAdmin 匯入有 foreign key 的資料表處理方案

匯入 fk 資料表時,就算所有關聯資料表一起匯入也會因為順序不一致而發生

這個錯誤

從網站上可以查詢到必須設定 SET FOREIGN_KEY_CHECKS=0; ,但是使用 SQL 指令執行後再使用 SELECT @@FOREIGN_KEY_CHECKS; 查看會發現還是 1 ,也就是沒有被關閉

解決的方法是修改匯入 .sql 檔案,在最開頭加上 SET FOREIGN_KEY_CHECKS=0; 以及結尾加上 SET FOREIGN_KEY_CHECKS=1;

類似像這樣

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

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

開啟 log

開啟 general-log 功能可以紀錄 MySQL 所有執行的 SQL 指令,

編輯 my.cnf

使用 SQL 指令修改 Log 狀態

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