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
– Patashu, Commented Feb 12, 2013 at 23:47

Jason McCreary · Accepted Answer · 2013-02-12 23:46:13Z

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, 2013 at 23:46

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Saqib Ali Over a year ago

so what are options? i need to use UTF to support international characters and special characters.......

Jason McCreary Over a year ago

Is this required for the key?

Saqib Ali Over a year ago

yea, we need to support international characters in the metadata_labels.label

Ja͢ck · Accepted Answer · 2013-02-13 12:43:50Z

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, 2013 at 12:43

answered Feb 12, 2013 at 23:51

Ja͢ck

174k39 gold badges269 silver badges317 bronze badges

5 Comments

Ja͢ck Over a year ago

@JasonMcCreary utf8 may require between 1 and 3 bytes of storage for each character.

Saqib Ali Over a year ago

is there is a way where i don't have to add another field(hash)?

Ja͢ck Over a year ago

@SaqibAli You could try to reduce the field width down to 250.

Jason McCreary Over a year ago

The question references utf16 which is 4 bytes.

Saqib Ali Over a year ago

Thanks @Jack. I have done that for now. But we will need to figure out a better DB schema

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related