Non-English characters count more with MySQL VARCHAR?

Question

Doesn't VARCHAR(15) mean you can have up to 15 characters in the field? If so, why am I getting 'Data too long' error?

I insert my data as 123456789 with no problem,

but with ١٢٣٤٥٦٧٨٩ I get

Data too long for column 'customer_phone' at row 1

they have the same number of characters!

My table definition (partly):

+-------------------+---------------+------+-----+-------------------+--------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+-------------------+---------------+ | id | int(11) | NO | PRI | NULL | auto_increment| | customer_id | int(11) | NO | MUL | NULL | | | customer_name | varchar(75) | YES | | NULL | | | customer_phone | varchar(15) | YES | | NULL | | | paid_amount | decimal(10,2) | NO | | NULL | | | paid_currency | varchar(4) | NO | MUL | NULL | | | ex_rate | decimal(8,2) | NO | | NULL | | | date_time | datetime | NO | MUL | CURRENT_TIMESTAMP | | | is_virtual | tinyint(1) | NO | | 0 | | +-------------------+---------------+------+-----+-------------------+---------------+

What is your character set ? example : show create table table_name; — Up_One, Mar 10 '14 at 18:44

score 1 · Answer 1 · edited Jul 10 '14 at 16:42

According to phpwact.org

... UTF-8 is a multibyte 8-bit encoding in which each Unicode scalar value is mapped to a sequence of one to four bytes. ...

Briefly, when you use utf8 your string can include characters with one to four bytes. I think english characters take one byte while Arabic / Persian characters take two bytes.

When you use varchar(N) it means you can insert N bytes. It means you can have N/2 arrabic characters inserted.

Conclusion : If you want insert a string with (N) Arabic / Persian characters then you must choose varchar(2*N).

Please let me know if this works, and what happens.

echo_Me · Answer 2 · 2014-03-10T19:29:10.940

0

Ensure that you set your connection character set to UTF8 prior to running the insertion query

you have to change the length of your column varchar(30) or change the collation to UTF8

  ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci

edited Mar 10 '14 at 19:29

answered Mar 10 '14 at 18:44

echo_Me

37,078
5
58
78

what do you mean by connection character set? I set it through `$mysqli->set_charset('utf8')` if that's what you mean? – mavili Mar 10 '14 at 18:47
you go to your database and change the collation of that column to UTF8 – echo_Me Mar 10 '14 at 18:49
the collation is `utf8_general_ci` and why would I need to set to `varchar(30)` if that realy means number of "characters" rather than some byte size? – mavili Mar 10 '14 at 18:49
to change to varchar(30) is just an option i said – echo_Me Mar 10 '14 at 18:52
I had already done that, but that's just strange! I shouldn't need to do that. even thou it does the job, I shall know why? – mavili Mar 10 '14 at 18:54
as i know , utf8 characters are counted double , you have to set utf8 collation. everywhere , your column , your editor , your files... – echo_Me Mar 10 '14 at 19:12
my Arabic is very bad, but I understood your message :) I have set everything to utf8 but I think unicode characters must be counted as double ! – mavili Mar 10 '14 at 19:20
You need to choose a utf8_* character set for your table. try my updated answer. – echo_Me Mar 10 '14 at 19:29
check this http://stackoverflow.com/questions/3328968/how-can-i-store-unicode-in-mysql – echo_Me Mar 10 '14 at 19:30

score 0 · Answer 3 · answered Mar 10 '14 at 18:48

Find the character set and make sure is set to UTF8.
to do so just add the follwing into your my.cnf file

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Non-English characters count more with MySQL VARCHAR?

3 Answers3