顯示具有 MySQL 標籤的文章。 顯示所有文章
顯示具有 MySQL 標籤的文章。 顯示所有文章

2019/07/30

MySQL 8.0 的新密碼加密 plugin 導致 PHP 連線失敗

今天很難得打開 phpMyAdmin 來看一下有什麼改變,但發現一直無法登入,顯示錯誤訊息「」。


查了一下才發現 MySQL 8 預設的密碼有多種格式,預設的格式 PHP 還不支援,所以會登入失敗 (但用 console 會成功)。

解決方法是暫時改為舊版本的密碼儲存方式:
alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-password';
flush privileges;

然後在設定檔裡面多新增一條規則,把預設的密碼儲存方式改回舊版的:
[mysqld]
default-authentication-plugin = mysql_native_password



2019/07/20

MySQL 8.0 不再使用的設定參數

以下幾個參數,在 MySQL 8.0 不在繼續支援、使用:
  • query-cache-type
  • query-cache-size
  • innodb_large_prefix

2019/04/25

幫 MySQL 加上 storage engine 設定來支援 utf8mb4

前篇有提到要在 my.ini 設定預設值,但實際要完全可以支援到 UTF8mb4 的話,還要多幾項設定,不然還是有機會遇到錯誤,像是:「The maximum column size is 767 bytes」之類的。

先講「innodb_large_prefix」,由於 UTF8mb4 會讓儲存相同內容的 bytes 更多,建立索引時會發生錯誤,所以必須支援 large preefix 來避免建立所以時發生錯誤。可以參考 MySQL 官方說明

在 MySQL 5.7 之後,InnoDB 新增個幾種儲存格式,像是 Barracuda,可以應付各種不同的情境,也可以支援 UTF8mb5 這種字元,所以要使用的話大概會需要這樣設定:
innodb_large_prefix
innodb_file_per_table = 1
innodb_file_format=Barracuda
innodb_default_row_format=DYNAMIC


完整的設定檔大概會類似下面這樣:
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
innodb_large_prefix
innodb_file_per_table = 1
innodb_file_format=Barracuda
innodb_default_row_format=DYNAMIC

[mysqldump]
default-character-set = utf8mb4

2019/04/11

讓 MySQL 預設使用特定的 character set

MySQL 支援多個不同的 character set,像是 latin1、utf8 等等,但是只要在規劃、連線時沒有做好設定,就很容易對應到錯誤的 character set 導致出現亂碼。

自己從 gslinPerconaStudy-Area 那邊整理出一些比較保險的設定方法,可以避免之後在維護時發生錯誤:
[client]
default-character-set = utf8mb4

[mysqld]
default-storage-engine         = InnoDB
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'


備註:
  • 以上設定檔適用於 MySQL / Percona server 5.7,不同版本有不同的 config keyword
  • 上面的範例為 utf8mb4,可自行調整成自己需要的 character set
  • 就算 MySQL server 有這些設定以後,連線到 MySQL 的程式也需要注意編碼設定,否則還是會發生編碼錯誤

2017/10/20

讓 MySQL 在查詢時區分英文大小寫

今天又遇到相同的問題,解法有很多種,可以從 SQL 下手,也可以在 table schema 就先做好設定。


SQL 強制區分大小寫


假設原本的 SQL 為:
SELECT * FROM users WHERE name = 'john' ;

這樣會撈出「John」、「john」等結果。那麼可以要求 MySQL 使用 binary 的辨識方法去做搜尋:
SELECT * FROM users WHERE binary name = 'johhn';

這樣一來,查詢時 name 欄位就會區分大小寫來做查詢。



修改 table schema


如果在設計 table 時,就確定查詢一定要區分大小寫時,可以在 create table 就先將欄位設定好:
CREATE TABLE users (
    name varchar(100) binary
)

這樣之後下 query 時,只要遇到 name 欄位,就自動會區分大小寫。



修改 collation type


一般常用的 collation 是「utf8_general_ci」,該 collation 最後面的「ci」其實是「Case Insensitive」的意思,也就是不區分大小寫。

如果要讓該 table 的所有欄位都區分大小寫,可以將 collation 的 postfix 改為「cs」或是「bin」,例如:
CREATE TABLE user (
    name varchar(100)
) COLLATE utf8_general_bin ;



以上三個區分大小寫的方法 scope 差異頗大的,可以挑比較適合當下情況的方法來使用。

2017/02/24

Nginx + PHP + MySQL 的 windows 懶人包

以前都是 WAMP 懶人包,現在終於看到已 Nginx + php-fpm + MySQL 為主的懶人包了:WPN-XM


WPN-XM 看起來已經有不少人在維護,新的 RC 版本也開始支援 PHP 7.x 了,看起來可以關注一下。

2016/10/15

增加 MySQL insert 速度的撇步

一次要新增大批資料到 MySQL 時,就會 MySQL 吃 CPU 又卡 I/O wait,這個時候如果沒時間吃下午茶的話實在會有點不爽 (?)

假設今天有個 table 長這個樣子:
mysql> desc users;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| id           | bigint(20) unsigned | NO   | PRI | NULL    |       |
| email        | varchar(200)        | YES  |     | NULL    |       |
| display_name | varchar(500)        | YES  |     | NULL    |       |
| reg_date     | int(10) unsigned    | YES  |     | NULL    |       |
| last_login   | int(10) unsigned    | YES  |     | NULL    |       |
| delete_at    | int(10) unsigned    | YES  |     | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+

登入、使用者管理等動作,我們會為以下幾個欄位加上 index 以加快查詢速度:
  • email
  • reg_date
  • last_login
  • delete_at

這個時候我們新增一筆資料時,MySQL 大概會做幾個動作:
  1. 新增一個 row
  2. 更新 index
    1. 更新 email index
    2. 更新 reg_date index
    3. 更新 last_login index
    4. 更新 delete_at index

所以如果只是把新增一筆資料,事情最多的不是建立一個 record,而是更新所有的 index。假設要新增的資料有 10000 筆,新增資料的 I/O 就當作 10000 次好了,更新 index 就另外再增加 40000 次 I/O。

若這 10000 筆資料是一次處理完,且不打算中斷,是否有發法可以減少 index 處理的次數?答案是 transaction。

當 10000 個 insertion 放在同一個 transaction 時,MySQL 會在 insertion 確認成功以後再來一次 update index。也就是說原本 update index 跑 40000 次,現在只需要跑 4 次。

由於 transaction 會造成 lock,新增資料前可以先試試看一個 transaction 要新增幾個 record,lock 的時間才不會影響到其他服務的運作。

ref:

2014/08/18

MySQL pager

MySQL 中的 query 結果太多時,超過螢幕高度就只能看到最後方的資料,要靠 terminal cache 才能看到前半部。

但其實 MySQL 有 pager 功能:
mysql> pager
Default pager wasn't set, using stdout.

把 pager 設定成 less:
mysql> pager less
PAGER set to 'less'

這樣一來,查詢結果就會送到 less 中,可以按照自己的閱讀速度捲動閱讀。

ps. pager 好像是綁 session,再次登入又要重新設定


ref:
Neat tricks for the MySQL command-line pager

2013/03/16

InnoDB 資料表獨立 ibdata1

前幾天閒閒沒事在玩 MySQL 大量資料寫入,可是忘記開的 InnoDB engine,/var 上 ibdata1 越來越肥,且把 database 砍掉肥油 (?) 還在。

原來要讓 ibdata1 減肥,必須將所有 DB 砍掉重建

但有也方法可以讓每個資料表有獨立的檔案,在 my.cnf 加入:
[mysqld]
innodb_file_per_table


Ref:
MySQL :: MySQL 5.0 Reference Manual :: 14.2.1.1 Using Per-Table Tablespaces
http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html

2011/06/11

轉貼:開元中最好的 web 開發資源

CoolShell.cn 網站上整理了不少網頁開發相關資源,包括:
  • 學習 HTML5 編成與設計
  • Server 端工具
  • PHP Framework 與工具
  • 資料庫
  • API 與即時資訊處理 (在線數據)
  • 線上軟體開發與媒體工具
  • 程式資源與版本管理工具
  • JavaScript Framework
  • JavaScript 移動與觸摸相關 framework
  • jQuery 外掛與相關資源
  • HTML5 視訊播放器
  • JavaScript 音訊處理與視覺化工具
  • JavaScript 2D、3D 圖形
  • JavaScript 與 HTML5 API (接口?)
  • 其他 JavaScript 工具
  • Client 端工具
  • CSS3 與字型資料
  • 網站樣板

忘了貼網址 XD

开源中最好的Web开发的资源

2011/05/25

MySQL Case Insensitive for Table Names

MySQL 的資料庫為一目錄、資料表為數檔案,MySQL server 會依照 SQL query 去讀取指定位置的檔案,但在 Windows 底下檔名不分大小寫、Linux 底下則有分大小寫。

在 Windows 中底下二個 SQL query 的結果相同:
select * from user_Name;
select * from user_Name; -- 不分大小寫,結果同上

但若是將做資料轉移從 Windows 轉到 Linux 後,卻會因為區分大小寫而造成 dump 回去的資料與 query 到的資料表被視為不同:
create table `user_Name` ( blablabla ..... );
select * from user_name; -- 大小寫不同撈不到資料

若遇到這情況,可以在 my.ini (或 my.cnf) 的 [mysqld] 標籤後加上「lower_case_table_names」參數,讓 MySQL server 處理指令時自動處理。

= 0:資料庫與資料表名稱均區分大小寫

= 1:資料庫與資料表檔案名稱均使用小寫,SQL 語法中的資料庫與資料表名稱均轉換成小寫再做比對

= 2:建立資料庫與資料表時檔案名稱區分大小寫,查詢時轉換成小寫再做比對,僅是用於不區分大小寫的檔案系統。


參考資料:MySQL case insensitive table and column names - Cosmin's Wiki
http://cosminswiki.com/index.php/MySQL_case_insensitive_table_and_column_names