CODEWITHSUNDEEP
mysqldatabaseintvarcharutf-16
Saqib Ali
Saqib Ali

Reputation: 4400

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

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

Upvotes: 0

Views: 929

Answers (2)

Ja͢ck
Ja͢ck

Reputation: 173522

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)

Upvotes: 1

Jason McCreary
Jason McCreary

Reputation: 72961

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.

Upvotes: 1

Related Questions