主页 / dba / 问题 / 305051
Accepted
DevelJoe
Asked: 2021-12-17 08:49:53 +0800 CST

为正确的数据存储确定理想的排序规则集

  • 772

好吧，我有一个 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
);

当您回显字符串时，这从未显示'编码，可能是因为它在回显时转换回单引号，但这只是一个假设。是的，做同样的事情\'而不是'存储也解决了这个问题。所以我想就是这样。

mariadb collation

2 个回答

  1. Best Answer

    在任何情况下，MySQL 都不会生成这 6 个字符： \u00e9从单个字符。

    '，也将 é 存储为\u00e9

    我认为这发生在您的客户端，而不是 MySQL。

    所以A列和B列现在使用完全相同的字符集+排序规则

    具有差异字符集和/或排序规则的不同列没有问题。当您比较具有不同排序规则的列时（尤其是在 中），可能会出现性能问题。JOIN...ON

    l'\u00e9l\u00e9phant，你得到的不是 l'éléphant，而是 l'éléphant

    那是不确定的。请注意，显示产品，尤其是 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 = '"硕士"'; // 带有 html 实体
    echo strlen($s), ' ', $s, ' ', bin2hex($s), " s - 带有 html 实体 \n";
    $t = '"硕士"'; // 反斜杠和撇号
    echo strlen($t), ' ', $t, ' ', bin2hex($t), " t - with backslash and apostrophe \n";
    回声“</pre>”；

    14 "Master's" 224d6173746572262333393b7322 s - 带有 html 实体
    10 "Master's" 224d6173746572277322 t - 带有反斜杠和撇号

    • 2

  2. 好吧，我真的无法弄清楚，但我的理论是，MariaDB JSON因此LONGTEXT列根本不喜欢单引号，并尽一切努力避免它。我个人解决&#39;问题的方法就是我在 PHP 中编写的这个回调（因为似乎没有内置函数可以递归地更新数组的键、值和值）：

    /**
  * Function which recursively applies a callback to all values and also its
  * keys, and returns the resulting array copy with the updated keys and
  * values.
  * PHP's built-in function array_walk_recursive() only applies the passed
  * callback to the array values, not the keys, so this function simply applies
  * the callback to the keys too (hence the need of working with a copy,
  * as also updating the keys would lead to reference loss of the original
  * array). I needed something like this, hence my idea of sharing it here.
  *
  * @param    callable    $func     callback which takes one parameter (value
  *                                                   or key to be updated) and returns its
  *                                                   updated value
  *
  * @param    array          $arr      array of which keys and values shall be
  *                                                   get updated
  */

function array_map_recursive(
    callable $func,
    array $arr
) {

      // Initiate copied array which will hold all updated keys + values
      $result = [];

      // Iterate through the key-value pairs of the array
      foreach ( $arr as $key => $value ) {

        // Apply the callback to the key to create the updated key value
        $updated_key = $func( $key );

        // If the iterated value is not an array, that means we have reached the
        // deepest array level for the iterated key, so in that case, assign
        // the updated value to the updated key value in the final output array
        if ( ! is_array( $value ) ) {

          $result[$updated_key] = $func( $value );

        } else {

          // If the iterated value is an array, call the function recursively,
          // By taking the currently iterated value as the $arr argument
          $result[$updated_key] = array_map_recursive(
            $func,
            $arr[$key]
          );

        }

      } // end of iteration through k-v pairs

      // And at the very end, return the generated result set
      return $result;

    } // end of array_map_recursive() function definition

    然后，您可以例如使用以下内容：

    $test = function( $to_update ) {
  return preg_replace( '/&#39;/', "'", $to_update );
}

    并调用，假设它$data包含您的所有数据：

    $no_more_weird_single_quotes = array_map_recursive( $test, $data );

    并且您的所有单引号，无论是在您的键还是值（这是我需要的）中，都将转换为实际的单引号。

    现在唯一的问题是插入到LONGTEXT我的 mariadb 中的列中的字符串包含单引号存储了转义版本，我猜这是出于安全原因。仍在为此寻找默认解决方案，我一定有什么感觉..

    现在都解决了，请参阅我的答案中的更新 3 + 4。

    • 0

相关问题