Determine Ideal Collation Set for correct data storage

Question

Alright so I have a MariaDB DB and I've just noticed some weird storage issues, like the following:

• My JSON columns (which in MariaDB are LONGTEXT) have an automatically set collation of utf8mb4_bin. I've just noticed that this actually messes up all of my apostrophes, by storing them as ', also stores é as \u00e9, etc.

• Some other columns hold french / spanish / portuguese etc. letters, and display é as é indeed. The collation it uses is utf8mb4_unicode_ci.

• Another column which holds strings which may include HTML markup uses utf8mb4_unicode_ci, and there are no issues with the markup whatsoever. But, it for some reason escapes single quotes, so it stores ' as \', which has to be avoided.

So my question is, which collation would you ideally use to not run into any issues with the storage of HTML markup, special characters as é, ö, ä, è, and not escape apostrophes, in MariaDB ?

UPDATE

Actually, I really don't get it why this happens:

• In my sample DB table, I have two columns, A and B. The Character set of the table is utf8mb4, and no column has a character set specifically assigned.

• The table uses the default collation utf8mb4_unicode_ci

• Column A uses collation utf8mb4_unicode_ci

• Column B used collation utf8mb4_bin

• Column A correctly stores letters as é, ä, etc.

• Column B stores them using their unicode, e.g. \u00e9 for é.

I've now changed column B's collation using:

ALTER TABLE sample_table MODIFY COLUMN column_b LONGTEXT COLLATE utf8mb4_unicode_ci.

So that column A and column B now use the exact same character set + collation. Data inserted into column_b is always a JSON_OBJECT.

Still, no matter the changes, only in column B but not in A, letters like é are still stored in their unicode-encoded format, and apostrophes are also stored as '. Any special letters like é are correctly retrieved when querying the data (if you query the data holding sth like l\u00e9ger, you correctly get léger).

BUT, if you query sth like l'\u00e9l\u00e9phant, you don't get l'éléphant, but l'éléphant.

I'm getting the data in PHP, and assuring that the character set of the connection is also utf8mb4, using mysqli_set_charset( $connection, 'utf8mb4' );.

I know I could theoretically simply code a search and replace on the data retrieved; but why the heck is this apostrophe conversion still happening?

UPDATE 2

Figured out a solution for the ' problem (see my posted answer), but now I'm trying to figure out a way for the other mentioned problem: When storing "Hello, I'm James" in a LONGTEXT data field, MariaDB stores Hello, I\'m James (it escapes the single quote in it, guess for safety reasons). Currently, when I then retrieve the data, I get

Hello, I\'m James

But I wanna get

Hello, I'm James

Even if the data is stored as

Hello, I\'m James

Of course you could again do search-replace stuff in PHP, but I just feel that there must be a standard approach in MariaDB, on the DB side, for this..?

UPDATE 3

Thanks to @Rick James' hint that no data character set conversion should occur in this case on the DB server side, I've been checking my server-side code, and indeed found the first problem concerning the unicode character issue (\u00e9 instead of é, etc.). The reason was that, before inserting the JSON into my DB, I've converted in from a PHP array into a JSON string using json_encode. The problem is that this escapes my unicode characters as \uXXXX by default, which should not be done in this case. To avoid this, instead of:

json_encode( $data )

Use:

json_encode( $data, JSON_UNESCAPED_UNICODE )

So that solved everything related to the unicode encoding errors. The problem concerning the weird apostrophe encoding to ' / the escaping to \' still remains unsolved.

UPDATE 4

Ok, also found the source of the ' and the \' problem, they were caused by the same problem. It was because I sanitized the string data used for the insert with:

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING
);

instead of:

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING,
  FILTER_FLAG_NO_ENCODE_QUOTES
);

This never showed the ' encoding when you echo the string out, probably because it's converted back to a single quote when echoed, but that's only an assumption. And yeah, doing the same where \' instead of ' was stored also solved that problem. So I guess that's it.

Answer 1

Under no conditions will MySQL generate these 6 characters: \u00e9 from a single character.

', also stores é as \u00e9

I think that happened in your client, not MySQL.

So that column A and column B now use the exact same character set + collation

There is no problem with different columns having diff charset and/or collation. A performance problem can occur when you compare columns with different collations (especially in JOIN...ON).

l'\u00e9l\u00e9phant, you don't get l'éléphant, but l'éléphant

That is inconclusive. Note that display products, especially HTML, will "clean up" things for you. To really see what is in the table, use SELECT HEX(col)...

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

l'éléphant encoded in UTF-8 and displayed in hex (with spaces added to separate characters):

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

I'm getting the data in PHP, and ...

But where is the data coming from? mysqli_set_charset is stating that it is utf8mb4-encoded, but is it really?

code a search and replace

If you rush into this, you could be making things worse. First let's find out what is really there, where it came from, etc.

I'm

That is proper in either of these string literals:

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

The language (PHP/MySQL/etc) will remove the backslash as it parses the string. But it is 'wrong' in other contexts.

it escapes the single quote

What escapes it?? prepare+execute? real_escape? addslashes? Something else? As implied above, you do need to escape it. But we need to know what did the escaping -- to avoid messing things up further.

Even if the data is stored as Hello, I'm James ...

You should not let it store that way. That just adds to the confusion later. Ditto for ' and \u00e9. The MySQL table should contain l'éléphant. I repeat, the only way to see if that is what it stored is via SELECT HEX(col) .... And expect "6C 27 C3A9 6C C3A9 70 68 61 6E 74" (minus spaces).

A test:

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

Usually you want this; without it, you bet the \unnnn codes:

json_encode($a, JSON_UNESCAPED_UNICODE)

Use urlencode() when you are going to put the string in a URL. That may be where %7C comes from.

PHP's htmlentities() can generate things like < and é. That last one is equivalent to '

In MySQL 8.0, you may need this technique:

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

which yields "Master's" (including the quotes).

PHP and its output:

echo "<pre>";
$s = '"Master's"'; // with html entity
echo strlen($s), ' ', $s, ' ', bin2hex($s), " s - with html entity \n";
$t = '"Master's"'; // backslash and apostrophe
echo strlen($t), ' ', $t, ' ', bin2hex($t), " t - with backslash and apostrophe \n";
echo "</pre>";

14 "Master's" 224d6173746572262333393b7322 s - with html entity
10 "Master's" 224d6173746572277322 t - with backslash and apostrophe

Answer 2

Well I couldn't really figure it out, but my theory is that MariaDB JSON and thus LONGTEXT columns simply don't like single quotes and do everything to avoid it. My personal workaround for the conversion to ' problem is simply this callback that I wrote in PHP (as there seems to be no built-in function to update BOTH keys and values and values of an array recursively):

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

You can then for example use the following:

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

And call, assuming that $data holds all of your data:

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

And all your single quotes, no matter if in your keys or values (which is what I needed) will be converted to actual single quotes.

Now there's only the problem that strings inserted into LONGTEXT columns in my mariadb holding single quotes store the escaped version of that, which I guess is for safety reasons. Still looking for a default solution for this, there must be something I feel..

All solved now, see Updates 3 + 4 in my answer.