I am using below mysql query to check which records vary from the trimmed value
SELECT id, BINARY(username) as binary_username, TRIM(username) as trim_username FROM table.
Above query returns binary value and trimmed value as shown below.
Result of mysql query:
Highlighted values in above image show that binary value vary from trimmed value.
I tried below 2 things:
calculating length of both binary and trimmed column but it is same
LENGTH(binary_username) != LENGTH(trim_username)
.equating them directly
binary_username != trim_username
.
but both of them are returning empty records.
How can I fetch these highlighted entries using mysql?
Edit 1: I have added HEX value in the query result
SELECT id, BINARY(username) as binary_username, TRIM(username) as trim_username, HEX(username) as hex_username FROM table
Thanks in advance…
Advertisement
Answer
To avoid storing, trimming, etc, the trailing zeros, use VARBINARY
instead of BINARY
. Why, pray tell, are you using BINARY for text strings??
Please do SELECT HEX(username) FROM ...
so we can further diagnose the problem. That screenshot is suspect — we don’t know what the client did to “fix” the output.
Well, none of those are encoded in UTF-8, nor anything else that I recognize. The ‘bad’ characters (02, 04, 0c 17), are all “control codes” in virtually all encodings. (“Unicode” is not an encoding method, so it is not relevant.)
Would you like a REGEXP that tests for control codes?
In PHP, json_encode has an option for JSON_UNESCAPED_UNICODE
. See https://www.php.net/manual/en/function.json-encode.php
But that generates u1234
type text.
When storing binary data into MySQL, use the binding or escaping mechanism in PDO or mysqli.