mySQL VARCHAR(256) + mySQL INT = how many bytes?

Question

CREATE SCHEMA IF NOT EXISTS `utftest`  DEFAULT CHARACTER SET utf16;
CREATE  TABLE IF NOT EXISTS `metadata_labels` (`metadata_id` INT NOT NULL , `label` VARCHAR(256) NOT NULL ,  PRIMARY KEY (`metadata_id`, `label`));

however I get the following error msg:

Specified key was too long; max key length is 767 bytes

Please advise

If possible, you should use an auto incrementing integer or GUID instead of a very long varchar as primary key — Patashu, Feb 12 '13 at 23:47

score 1 · Accepted Answer · answered Feb 12 '13 at 23:46

1

UTF 16 uses 32 bits per character (4 bytes) in MySQL. 4 x 256 > 767.

If possible, I would recommend using something other than UTF16 VARCHAR for your key.

answered Feb 12 '13 at 23:46

Jason McCreary

71,546
23
135
174

so what are options? i need to use UTF to support international characters and special characters....... – Saqib Ali Feb 12 '13 at 23:50
Is this required for the key? – Jason McCreary Feb 12 '13 at 23:50
yea, we need to support international characters in the metadata_labels.label – Saqib Ali Feb 12 '13 at 23:52

Ja͢ck · Answer 2 · 2013-02-13T12:43:50.163

1

In UTF8, it would require 3 x 256 + 4 = 772 bytes. UTF16 would take another 25% more.

You shouldn't use a primary key that's so wide; for an index to be efficient, the storage for each index should be kept to a minimum.

If you need to prevent duplicates, I would recommend adding a calculated field that contains a hash of the contents (e.g. sha1) and create a unique constraint on that instead.

Alternatively, use latin1 as the character encoding for the label field to reduce the number of bytes to 256 + 4 = 300.

If Unicode is a must and hashes are out of the picture you should reduce the column to either UTF8 (250 chars) or UTF16 (190 chars)

edited Feb 13 '13 at 12:43

answered Feb 12 '13 at 23:51

Ja͢ck

170,779
38
263
309

@JasonMcCreary utf8 may require between 1 and 3 bytes of storage for each character. – Ja͢ck Feb 12 '13 at 23:53
is there is a way where i don't have to add another field(hash)? – Saqib Ali Feb 12 '13 at 23:56
1

@SaqibAli You could try to reduce the field width down to 250. – Ja͢ck Feb 13 '13 at 00:01
1

The question references `utf16` which is 4 bytes. – Jason McCreary Feb 13 '13 at 12:34
Thanks @Jack. I have done that for now. But we will need to figure out a better DB schema – Saqib Ali Feb 13 '13 at 15:01

mySQL VARCHAR(256) + mySQL INT = how many bytes?

2 Answers2