user3299633 user3299633 - 1 year ago 93
MySQL Question

mysql substring/left command isnt returning correct number of characters

I'm executing the following statement:

select left(column,400) from table into outfile test;

I've also tried using substring function (with same results).

When I go to download the file and get a character count:

wc -c < test

I get 409 characters as a return.

Come someone assist me in why I'm getting an incorrect count?

The database table is set to utf8 and the column is longtext.

When I run the following it still doesn't give me correct length of characters:

select length(left(column, 400) from table where id in (1,2,3,4);
| length(left(column,400)) |
| 402 |
| 403 |
| 412 |
| 401 |

Answer Source

The command wc -c is counting bytes, despite the character used for the switch. With the DB in UTF-8, the mysql left is counting characters. Since UTF-8 can use more than 1 byte per character, I expect the first 400 characters in column includes 8 characters that take 2 bytes (or less than 8 if some take 3 bytes). There's probably a newline at the end as well.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download