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.



  • 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

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


Multibyte characters will have a greater LENGTH (bytes), so you'll need to look for where that condition isn't met.

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
  • 5
    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
  • Tried this on my users table and I get the error #1054 - Unknown column 'users' in 'where clause' - SELECT * FROM users WHERE LENGTH(users) <> CHAR_LENGTH(users) LIMIT 0, 25 Dec 11 '20 at 18:03

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.