Início / dba / Perguntas / 305051
Accepted
DevelJoe
Asked: 2021-12-17 08:49:53 +0800 CST

Determine o conjunto de agrupamento ideal para armazenamento de dados correto

  • 772

Tudo bem, então eu tenho um banco de dados MariaDB e acabei de notar alguns problemas estranhos de armazenamento, como o seguinte:

  • Minhas JSONcolunas (que no MariaDB são LONGTEXT) têm um agrupamento definido automaticamente de utf8mb4_bin. Acabei de notar que isso realmente atrapalha todos os meus apóstrofos, armazenando-os como ', também armazena écomo \u00e9, etc.

  • Algumas outras colunas contêm letras francesas / espanholas / portuguesas etc. e são exibidas écomo de éfato. O agrupamento que ele usa é utf8mb4_unicode_ci.

  • Outra coluna que contém strings que podem incluir marcação HTML usa utf8mb4_unicode_ci, e não há problemas com a marcação. Mas, por algum motivo, escapa das aspas simples, portanto, armazena 'como \', o que deve ser evitado.

Então, minha pergunta é, qual agrupamento você usaria idealmente para não ter problemas com o armazenamento de marcação HTML, caracteres especiais como é, ö, ä, è, e não apóstrofos de escape, no MariaDB?

ATUALIZAR

Na verdade, eu realmente não entendo porque isso acontece:

  • Na minha tabela de banco de dados de exemplo, tenho duas colunas, A e B. O conjunto de caracteres da tabela é utf8mb4, e nenhuma coluna tem um conjunto de caracteres atribuído especificamente.

  • A tabela usa o agrupamento padrãoutf8mb4_unicode_ci

  • A coluna A usa agrupamentoutf8mb4_unicode_ci

  • Agrupamento usado da coluna Butf8mb4_bin

  • A coluna A armazena corretamente letras como é, ä, etc.

  • A coluna B os armazena usando seu unicode, por exemplo, \u00e9para é.

Agora alterei o agrupamento da coluna B usando:

ALTER TABLE sample_table MODIFY COLUMN column_b LONGTEXT COLLATE utf8mb4_unicode_ci.

Portanto, a coluna A e a coluna B agora usam exatamente o mesmo conjunto de caracteres + agrupamento. Os dados inseridos column_bsão sempre um arquivo JSON_OBJECT.

Ainda assim, não importa as alterações, apenas na coluna B, mas não na A, letras como éainda são armazenadas em seu formato codificado em unicode, e apóstrofos também são armazenados como '. Quaisquer letras especiais como ésão recuperadas corretamente ao consultar os dados (se você consultar os dados que contêm sth like l\u00e9ger, você obterá corretamente léger).

MAS, se você consultar sth like l'\u00e9l\u00e9phant, você não obterá l'éléphant, mas l'éléphant.

Estou pegando os dados em PHP, e garantindo que o conjunto de caracteres da conexão também seja utf8mb4, usando mysqli_set_charset( $connection, 'utf8mb4' );.

Eu sei que teoricamente poderia simplesmente codificar uma pesquisa e substituir os dados recuperados; mas por que diabos essa conversão de apóstrofo ainda está acontecendo?

ATUALIZAÇÃO 2

Descobri uma solução para o 'problema (veja minha resposta postada), mas agora estou tentando descobrir uma maneira para o outro problema mencionado: Ao armazenar "Hello, I'm James"em um LONGTEXTcampo de dados, o MariaDB armazena Hello, I\'m James(ele escapa das aspas simples, acho por razões de segurança). Atualmente, quando recupero os dados, recebo

Hello, I\'m James

Mas eu quero pegar

Hello, I'm James

Mesmo que os dados sejam armazenados como

Hello, I\'m James

Claro que você poderia novamente fazer coisas de substituição de pesquisa em PHP, mas eu sinto que deve haver uma abordagem padrão no MariaDB, no lado do banco de dados, para isso ..?

ATUALIZAÇÃO 3

Graças à dica de @Rick James de que nenhuma conversão de conjunto de caracteres de dados deve ocorrer neste caso no lado do servidor de banco de dados, verifiquei meu código do lado do servidor e, de fato, encontrei o primeiro problema relacionado ao problema de caractere unicode ( \u00e9em vez de é, etc.). O motivo foi que, antes de inserir o JSON no meu banco de dados, converti de um array PHP em uma string JSON usando json_encode . O problema é que isso escapa dos meus caracteres unicode \uXXXXpor padrão, o que não deve ser feito nesse caso. Para evitar isso, em vez de:

json_encode( $data )

Usar:

json_encode( $data, JSON_UNESCAPED_UNICODE )

Então isso resolveu tudo relacionado aos erros de codificação unicode. O problema relativo à estranha codificação do apóstrofo para '/ o escape para \'ainda permanece sem solução.

ATUALIZAÇÃO 4

Ok, também encontrei a fonte do 'problema \'e foram causados ​​pelo mesmo problema. Foi porque eu higienizei os dados de string usados ​​para a inserção com:

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING
);

ao invés de:

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING,
  FILTER_FLAG_NO_ENCODE_QUOTES
);

Isso nunca mostrou a 'codificação quando você ecoa a string, provavelmente porque ela é convertida de volta para uma aspa simples quando ecoada, mas isso é apenas uma suposição. E sim, fazer o mesmo onde \'em vez de 'foi armazenado também resolveu esse problema. Então acho que é isso.

mariadb collation

2 respostas

  1. Best Answer

    Sob nenhuma condição o MySQL irá gerar estes 6 caracteres: \u00e9a partir de um único caractere.

    ', também armazena é como\u00e9

    Acho que isso aconteceu no seu cliente, não no MySQL.

    Para que a coluna A e a coluna B agora usem exatamente o mesmo conjunto de caracteres + agrupamento

    Não há problema com colunas diferentes com conjunto de caracteres diff e/ou agrupamento. Um problema de desempenho pode ocorrer quando você compara colunas com agrupamentos diferentes (especialmente em JOIN...ON).

    l'\u00e9l\u00e9phant, você não recebe l'éléphant, mas l'éléphant

    Isso é inconclusivo. Observe que os produtos de exibição, especialmente HTML, "limparão" as coisas para você. Para realmente ver o que está na tabela, useSELECT HEX(col)...

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

    l'éléphantcodificado em UTF-8 e exibido em hexadecimal (com espaços adicionados a caracteres separados):

    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

    Estou pegando os dados em PHP, e...

    Mas de onde vêm os dados? mysqli_set_charsetestá afirmando que é utf8mb4codificado, mas é realmente?

    codifique uma pesquisa e substitua

    Se você se apressar nisso, você pode estar piorando as coisas. Primeiro vamos descobrir o que realmente existe, de onde veio, etc.

    Eu estou

    Isso é apropriado em qualquer um desses literais de string:

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

    A linguagem (PHP/MySQL/etc) removerá a barra invertida à medida que analisa a string. Mas é 'errado' em outros contextos.

    escapa das aspas simples

    O que escapa?? preparar+executar? real_escape? adiciona barras? Algo mais? Como implícito acima, você precisa escapar dele. Mas precisamos saber o que fez a fuga - para evitar estragar ainda mais as coisas.

    Mesmo que os dados sejam armazenados como Olá, sou James...

    Você não deve deixá-lo armazenar dessa maneira. Isso só aumenta a confusão mais tarde. Idem para 'e \u00e9. A tabela MySQL deve conter l'éléphant. Repito, a única maneira de ver se é isso que ele armazenou é via SELECT HEX(col) .... E espere "6C 27 C3A9 6C C3A9 70 68 61 6E 74" (menos espaços).

    Um teste:

    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 |
+------------------+----------------------------------+-------------------------+------------------------------+

    Normalmente você quer isso; sem ele, você aposta os \unnnncódigos:

    json_encode($a, JSON_UNESCAPED_UNICODE)

    Use urlencode()quando for colocar a string em uma URL. Pode ser de onde vem %7C.

    PHP htmlentities()pode gerar coisas como <e é. Esse último é equivalente a'

    No MySQL 8.0, você pode precisar desta técnica:

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

    que rende "Master's"(incluindo as aspas).

    PHP e sua saída:

    echo "<pre>";
    $s = '"Mestre"'; // com entidade html
    echo strlen($s), ' ', $s, ' ', bin2hex($s), " s - com entidade html \n";
    $t = '"Mestre"'; // barra invertida e apóstrofo
    echo strlen($t), ' ', $t, ' ', bin2hex($t), " t - com barra invertida e apóstrofo \n";
    echo "</pre>";

    14 "Mestres" 224d6173746572262333393b7322 s - com entidade html
    10 "Mestres" 224d6173746572277322 t - com barra invertida e apóstrofo

    • 2

  2. Bem, eu realmente não consegui descobrir, mas minha teoria é que MariaDB JSON, portanto, LONGTEXTas colunas simplesmente não gostam de aspas simples e fazem de tudo para evitá-las. Minha solução pessoal para a conversão para o &#39;problema é simplesmente este retorno de chamada que escrevi em PHP (já que parece não haver nenhuma função interna para atualizar ambas as chaves e valores e valores de uma matriz recursivamente):

    /**
  * 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

    Você pode então, por exemplo, usar o seguinte:

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

    E ligue, assumindo que $datacontém todos os seus dados:

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

    E todas as suas aspas simples, não importa se em suas chaves ou valores (que é o que eu precisava), serão convertidas em aspas simples reais.

    Agora há apenas o problema de que as strings inseridas em LONGTEXTcolunas no meu mariadb com aspas simples armazenam a versão com escape disso, o que acho que é por motivos de segurança. Ainda procurando uma solução padrão para isso, deve haver algo que eu sinto ..

    Tudo resolvido agora, veja as atualizações 3 + 4 na minha resposta.

    • 0

relate perguntas