為正確的數據儲存確定理想的排序規則集

好的，所以我有一個 MariaDB 數據庫，我剛剛注意到一些奇怪的儲存問題，如下所示：

• 我的JSON列（在 MariaDB 中是LONGTEXT）有一個自動設置的排序規則utf8mb4_bin。我剛剛注意到這實際上弄亂了我所有的撇號，將它們儲存為'，也儲存é為\u00e9等。
• 其他一些列包含法語/西班牙語/葡萄牙語等字母，並且é確實é顯示。它使用的排序規則是utf8mb4_unicode_ci.
• 另一列包含可能包含 HTML 標記的字元串，utf8mb4_unicode_ci並且標記沒有任何問題。但是，由於某種原因，它轉義了單引號，因此它儲存'為\'，必須避免。

所以我的問題是，在 MariaDB 中，您最好使用哪種排序規則來避免在儲存 HTML 標記、特殊字元如 é、ö、ä、è 以及不轉義撇號時遇到任何問題？

更新

實際上，我真的不明白為什麼會這樣：

• 在我的範例數據庫表中，我有兩列，A 和 B。表的字元集是utf8mb4，並且沒有一列有專門分配的字元集。
• 該表使用預設排序規則utf8mb4_unicode_ci
• A 列使用排序規則utf8mb4_unicode_ci
• B 列使用了排序規則utf8mb4_bin
• A 列正確地將字母儲存為é、ä等。
• B 列使用它們的 unicode 儲存它們，例如\u00e9for é。

我現在使用以下方法更改了 B 列的排序規則：

ALTER TABLE sample_table MODIFY COLUMN column_b LONGTEXT COLLATE utf8mb4_unicode_ci.

因此，A 列和 B 列現在使用完全相同的字元集 + 排序規則。插入column_b的數據始終是JSON_OBJECT.

儘管如此，無論更改如何，僅在 B 列而不是 A 列中，字母 likeé仍以其 unicode 編碼格式儲存，撇號也儲存為'. 查詢數據時，任何特殊字母 like 都會é被正確檢索（如果您查詢數據持有 sth like l\u00e9ger，則正確得到léger）。

但是，如果你查詢 sth like l'\u00e9l\u00e9phant，你不會得到l'éléphant, 但是l'éléphant。

我在 PHP 中獲取數據，並確保連接的字元集也是utf8mb4, 使用mysqli_set_charset( $connection, 'utf8mb4' );.

我知道理論上我可以簡單地對檢索到的數據進行搜尋和替換；但是為什麼這種撇號轉換還在發生呢？

更新 2

找到了'問題的解決方案（請參閱我發布的答案），但現在我正試圖找出解決另一個提到的問題的方法：當儲存"Hello, I'm James"在LONGTEXT數據欄位中時，MariaDB 儲存Hello, I\'m James（它轉義了其中的單引號，猜出於安全原因）。目前，當我檢索數據時，我得到

Hello, I\'m James

但我想得到

Hello, I'm James

即使數據儲存為

Hello, I\'m James

當然，您可以再次在 PHP 中進行搜尋替換，但我只是覺得 MariaDB 中必須有一個標準方法，在數據庫方面，為此..？

更新 3

感謝@Rick James 的提示，在這種情況下，在數據庫伺服器端不應該發生數據字元集轉換，我一直在檢查我的伺服器端程式碼，確實發現了關於 unicode 字元問題的第一個問題（\u00e9而不是é, ETC。）。原因是，在將 JSON 插入我的數據庫之前，我已經使用json_encode從 PHP 數組轉換為 JSON 字元串。問題是\uXXXX預設情況下這會轉義我的 unicode 字元，在這種情況下不應該這樣做。為了避免這種情況，而不是：

json_encode( $data )

採用：

json_encode( $data, JSON_UNESCAPED_UNICODE )

這樣就解決了與 unicode 編碼錯誤相關的所有問題。關於奇怪的撇號編碼到 '/轉義到的\'問題仍未解決。

更新 4

好的，也找到了問題的根源'，\'它們是由同一個問題引起的。這是因為我使用以下方法清理了用於插入的字元串數據：

filter_var(
 $my_string,
 FILTER_SANITIZE_STRING
);

代替：

filter_var(
 $my_string,
 FILTER_SANITIZE_STRING,
 FILTER_FLAG_NO_ENCODE_QUOTES
);

當您回顯字元串時，這從未顯示'編碼，可能是因為它在回顯時轉換回單引號，但這只是一個假設。是的，做同樣的事情\'而不是'儲存也解決了這個問題。所以我想就是這樣。

在任何情況下，MySQL 都不會生成這 6 個字元： \u00e9從單個字元。

‘，商店也一樣\u00e9

我認為這發生在您的客戶端，而不是 MySQL。

所以A列和B列現在使用完全相同的字元集+排序規則

具有差異字元集和/或排序規則的不同列沒有問題。當您比較具有不同排序規則的列時（尤其是在 中），可能會出現性能問題。JOIN...ON

大象，你得到的不是大象，而是大象

那是不確定的。請注意，顯示產品，尤其是 HTML，將為您“清理”一些東西。要真正查看表格中的內容，請使用SELECT HEX(col)...

'  -- "HTML entity"
\u00e9 -- "unicode" encoding

l'éléphant以 UTF-8 編碼並以十六進制顯示（在單獨的字元中添加空格）：

Double encoding:  6C 27 C383C2A9 6C C383C2A9 70 68 61 6E 74  
UTF-8 encoding:   6C 27   C3A9   6C   C3A9   70 68 61 6E 74 
latin1 encoding:  6C 27    E9    6C    E9    70 68 61 6E 74
text:              l  '     é     l     é     p  h  a  n  t

我在 PHP 中獲取數據，並且…

但數據從何而來？ mysqli_set_charset是說它是utf8mb4編碼的，但真的嗎？

編碼搜尋和替換

如果你急於這樣做，你可能會讓事情變得更糟。首先讓我們找出真正存在的東西，它來自哪裡等。

我是

這在以下任一字元串文字中都是正確的：

'I\'m'
"I\'m"

語言 (PHP/MySQL/etc) 將在解析字元串時刪除反斜杠。但在其他情況下它是“錯誤的”。

它轉義了單引號

什麼逃脫它？準備+執行？real_escape？加斜線？還有什麼？如上所述，您確實需要逃避它。但是我們需要知道逃跑的原因——以避免進一步搞砸事情。

即使數據儲存為你好，我是詹姆斯……

你不應該讓它以這種方式儲存。這只會增加後來的混亂。'和同上\u00e9。MySQL 表應包含l'éléphant. 我再說一遍，查看它是否儲存的唯一方法是通過SELECT HEX(col) .... 並期望“6C 27 C3A9 6C C3A9 70 68 61 6E 74”（減去空格）。

一個測試：

mysql> INSERT INTO try_json (j) VALUES ('["I\'m"]');
mysql> INSERT INTO try_json (j) VALUES ('["l\'éléphant"]');
mysql> SELECT j, HEX(j), JSON_EXTRACT(j, '$[0]'), HEX(JSON_EXTRACT(j, '$[0]')) FROM try_json;
+------------------+----------------------------------+-------------------------+------------------------------+
| j                | HEX(j)                           | JSON_EXTRACT(j, '$[0]') | HEX(JSON_EXTRACT(j, '$[0]')) |
+------------------+----------------------------------+-------------------------+------------------------------+
| ["I'm"]          | 5B2249276D225D                   | "I'm"                   | 2249276D22                   |
| ["l'éléphant"]   | 5B226C27C3A96CC3A97068616E74225D | "l'éléphant"            | 226C27C3A96CC3A97068616E7422 |
+------------------+----------------------------------+-------------------------+------------------------------+

通常你想要這個；沒有它，你打賭\unnnn程式碼：

json_encode($a, JSON_UNESCAPED_UNICODE)

urlencode()當您要將字元串放入 URL 時使用。這可能就是 %7C 的來源。

PHPhtmlentities()可以生成諸如<和之類的東西é。最後一個相當於'

在 MySQL 8.0 中，您可能需要這種技術：

select cast(unhex('224D6173746572262333393B7322') as char);

產生"Master's"（包括引號）。

PHP 及其輸出：

迴聲“<pre>”；

$ s = ‘“Master’s”’; // with html entity echo strlen( $ s), ’ ‘, $ s, ’ ‘, bin2hex( $ s), " s - 帶有 html 實體 \n";

$ t = ‘“Master’s”’; // backslash and apostrophe echo strlen( $ t), ’ ‘, $ t, ’ ‘, bin2hex( $ t), " t - 帶有反斜杠和撇號 \n";

迴聲“</pre>”；

14 “Master’s” 224d6173746572262333393b7322 s - 帶有 html 實體

10 “Master’s” 224d6173746572277322 t - 帶有反斜杠和撇號

引用自：https://dba.stackexchange.com/questions/305051