Alright so I have a MariaDB DB and I've just noticed some weird storage issues, like the following:
JSONcolumns (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
Some other columns hold french / spanish / portuguese etc. letters, and display
éindeed. The collation it uses is
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
\', 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 ?
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
Column A uses collation
Column B used collation
Column A correctly stores letters as
Column B stores them using their unicode, e.g.
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
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
BUT, if you query sth like
l'\u00e9l\u00e9phant, you don't get
I'm getting the data in PHP, and assuring that the character set of the connection is also
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?
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..?
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 )
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.
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 );
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.