4

Ok so I have a mysql database containing data that is input from various customers. The customers are told that their data should only be encoded as utf8 however I have become aware that this is currently not being checked and some people are using characters not encoded in utf8. We intend to add some checking but in order to clean the data already there is there a sql or mysql query (or admin command) that will show me the data that is NOT encoded as utf8.

Sorry if this is a dumb question I'm a Linux Sysadmin and have limited exposure to databases.

Thanks

B

migrated from serverfault.com Sep 19 '14 at 16:17

This question came from our site for system and network administrators.

  • Could you provide more details? Are your clients (potentially) creating non-utf8 fields, are they storing non-utf8 strings in utf8 fields or in binary strings? – jynus Sep 19 '14 at 16:28
11

You can check for the existence of (non-)UTF-8 data by comparing byte length to character length on a column, e.g.:

SELECT * FROM MyTable
WHERE LENGTH(MyColumn) = CHAR_LENGTH(MyColumn)

Multibyte characters will have a greater LENGTH (bytes).

Note that MySQL's utf8 character set isn't true Unicode UTF-8 as it only supports a maximum of 3 bytes per character. If your MySQL is later than 5.5.3 you can use utf8mb4 to get 4 bytes per character.

  • So in my case using SELECT * FROM TABLE where length(COLUMN) > char_length(COLUMN) worked a treat and returned the entries that had none utf8. Thanks very much really appreciated do I need to award points and/or mark this as answered somehow? – Bret Sep 21 '14 at 19:33
  • Had to look it up because I haven't asked a question: meta.stackexchange.com/questions/5234/…. Just click the check mark beside the answer. – dartonw Sep 22 '14 at 0:10
  • 2
    I don't think this is right. UTF-8 can encode characters in 1 to 4 bytes, so how does comparing character length to byte length reveal non-UTF-8 data? One byte per character only contains (roughly) ASCII characters, so the question that you're answering here is how to find non ASCII chars, not non-UTF-8 chars. Am I missing something? – goron Sep 25 '16 at 22:35
  • this is literally genius. – caro Aug 29 '17 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.