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.