1

I'm on my way to converting my UTF8 database to UTF8MB4.

I've read some advice about the fact that VARCHAR(255) fields should be turned to VARCHAR(191).

cf. https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4

But : as a VARCHAR field can store up to 65,535 bytes, I am planning to keep VARCHAR(255) — and sometimes VARCHAR(400) — after converting my UTF8 database to UTF8MB4. (I'll just re-create the indexes).

I see no benefit to force and limit my fields to VARCHAR(191).

This way I'll still be able to store 255 characters, even if all of these characters are... 255 emojis ! 😎

Am I right, what could prevent me to do this ?

1
  • I've read some advice about the fact that VARCHAR(255) fields should be turned to VARCHAR(191). No such need for a column. Maybe you tell about indexed column? The index have a limit of 768 bytes per value which results in above column length (or column prefix length in the index) change.
    – Akina
    Jan 18 at 5:54

2 Answers 2

3

Sure, you can use a VARCHAR length up to 65535 bytes. When using utf8mb4, allow for up to 4 bytes per character, so the effective maximum length is 65535/4 characters, or 16383.

mysql> create table t ( v varchar(65535));
ERROR 1074 (42000): Column length too big for column 'v' (max = 16383); use BLOB or TEXT instead

mysql> create table t ( v varchar(16383));
Query OK, 0 rows affected (0.01 sec)

The shorter length mentioned in that blog is for string columns if you want to add an index on that column.

mysql> alter table t add index (v);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

The maximum length of an index was 768 bytes in MySQL 5.5, which was the current version in 2012 when that blog was written.

In the current version of MySQL 8.0, the default InnoDB row format allows an index up to 3072 bytes. So you can use VARCHAR(768) and still make an index for it.

mysql> alter table t 
    modify column v varchar(768), 
    add index (v);
Query OK, 0 rows affected (0.02 sec)

You could alternatively make a much longer column, and make an prefix index on the first N characters, up to 3072 bytes.

mysql> alter table t 
    drop index v, 
    modify column v varchar(16383), 
    add index (v(768));
Query OK, 0 rows affected (0.01 sec)

In general, I don't like seeing VARCHAR(255) used ubiquitously. I've seen it used inappropriately, for strings that are never going to require that much length. I've seen it used for:

  • Person's last name
  • Postal codes
  • Credit card types (e.g. VISA, MC, AMEX)
  • etc.

It would be better to use a more appropriate length for the data you store. This has the advantage that if someone tries to store a string that isn't meant for that column, they get an error message. Often the principle of "fail early" is better than "allow invalid data, and the months later sense subtle failures."

If you do need to store longer strings, is 255 long enough? Why not use the longest length allowed by VARCHAR? Or else just use TEXT?

The use of 255 as a default is based on the true implementation that the length can be encoded in one byte internally. If you use a length of 256 or greater, then MySQL uses more bytes to encode the length of each string value. So you get a tiny bit more overhead per row. But if you're storing long strings in that column, the one extra byte of overhead is small compared to the string itself.

0
1

Sincere Thanks @BillKarwin for your precise, documented, and useful reply, as well to Rick for its contribution.

To keep it clear, let's assume I'm now on a UTF8MB4 database, so that each character can take up to 4 bytes (instead of 3 bytes in legacy MySQL/MariaDB "UTF8").

As I'm using different versions of MySQL and sometimes MariaDB, let's simplify and assume I'm running on MySQL 5.6 so the max index size is 768 bytes.


So to my understanding of your reply :

  • I can create VARCHAR fields up to 65535 bytes/4 characters, so a max of 16383 characters. That's great !
  • I can create LONGTEXT fields. That's great !

I had a couple of questions concerning indexes in the light of the elements your brought (because I'm not so good in this indexing matter ^!^), and I finally sorted this out :

After re-reading the official documentation, I finally understood that :

  • Prefix limits are MEASURED in bytes
  • but are EXPRESSED in number of characters.

I then conclude that I should never use more than 767/4=191 characters in my prefix index definition.

  • So when I need to index fields that exceed 191 characters in UTF8MB4 (because 191*4 = 764 bytes), I'll need to create partial indexes (prefix index) that are expressed in number of characters, this number not being above 191.

Here are the SQL statements I now consider valid :

CREATION OF NEW TABLE FROM FRESH

CREATE TABLE myTable (
    id int(11) NOT NULL AUTO_INCREMENT,
    someText_32 varchar(32) DEFAULT '',
    someText_255 varchar(255) DEFAULT '',
    someText_256 varchar(256) DEFAULT '',
    someText_400 varchar(400) DEFAULT '',
    someLongText longtext,
    PRIMARY KEY (id) USING BTREE,
    KEY ix_someText_32 (someText_32(32)) USING BTREE,
    KEY ix_someText_255 (someText_255(191)) USING BTREE,
    KEY ix_someText_256 (someText_256(191)) USING BTREE,
    KEY ix_someText_400 (someText_400(191)) USING BTREE,
    KEY ix_someLongText (someLongText(191)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

ALTERATION OF EXISTING TABLE

ALTER TABLE myTable DROP INDEX ix_someText_32, MODIFY COLUMN someText_32 varchar(32), ADD INDEX (v(32));
ALTER TABLE myTable DROP INDEX ix_someText_255, MODIFY COLUMN someText_255 varchar(255), ADD INDEX (v(191));
ALTER TABLE myTable DROP INDEX ix_someText_256, MODIFY COLUMN someText_256 varchar(256), ADD INDEX (v(191));
ALTER TABLE myTable DROP INDEX ix_someText_400, MODIFY COLUMN someText_400 varchar(400), ADD INDEX (v(191));
ALTER TABLE myTable DROP INDEX ix_someLongText, MODIFY COLUMN someLongText longtext, ADD INDEX (v(191));
6
  • 2
    There is no need for the "prefix" of "(768)" in KEY ix_someText_400 (someText_400(768)) USING BTREE, since that number is characters, not bytes.
    – Rick James
    Jan 17 at 23:58
  • I agree with Rick — there is never a need to declare a prefix length of an index longer than the column itself. It is needed for the LONGTEXT column, because that can be much longer than the max index size. Jan 18 at 0:22
  • 1
    Given your assumption of the max index size of 768 bytes, you cannot use a prefix length of 768 characters on a character set that is multi-byte. Jan 18 at 0:24
  • 1
    Correct. It may seem confusing, but I think they found the least confusing compromise. If your prefix length were expressed in bytes, but it actually applied to a different number of characters if you used a multi-byte character set, that would have been even more confusing. Jan 18 at 15:05
  • 3
    @AlexLaforge - please don't add [Updated to make it more of an answer] to your answer, just update it to make it an actual answer. This isn't a forum, it's a place where future users can find concise answers that answer a specific question. You don't need to have a conversation in your answer, just add the actual specific details. Making a future reader have to read a conversation to understand how to fix the issue is not very helpful.
    – Hannah Vernon
    Jan 18 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.