2

I have already read myself here and there, but have not found an answer that answers my question... I now have a database utf8mb4 and want to store a string in a column (length 200-250 characters). Is VARCHAR (200 - 250) now OK for utf8mb4 or do I have to use TEXT?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Norman Huth
  • 519
  • 5
  • 16

2 Answers2

1

Please see this article on MySQL docs:

In terms of table structure, these are the primary potential incompatibilities:

  • For the variable-length character data types (VARCHAR and the TEXT types), the maximum permitted length in characters is less for utf8mb4 columns than for utf8mb3 columns.

  • For all character data types (CHAR, VARCHAR, and the TEXT types), the maximum number of characters that can be indexed is less for utf8mb4 columns than for utf8mb3 columns.

So the VARCHAR field will not support your column data, since it reserve space just for 3-bytes character (utf8mb4, as the name say, uses 4-bytes).

In this case, you will need to use another column type, like TEXT.

The quoted article have more details about it, I recommend you to read it.

Edit:

Reading more, I realized that the VARCHAR datatype actually supports up to 65535 characters (since MySQL 5.0.3) and 65535 bytes. So the actual maximum length in chars is 65535 / 4 = 16383 chars.

So yes, your VARCHAR column will support your 250 chars long string.

Elias Soares
  • 9,884
  • 4
  • 29
  • 59
  • Varchar has maximum byte size limit of 65535 bytes. Even if it is 4-byte character, it can still accomodate 65535/4 = 16383 characters. That is **more than enough** for OP who needs only 200-250 chracters. Moreover, `Text` datatype cannot be indexed; while `Varchar` can be indexed – Madhur Bhaiya Oct 30 '18 at 17:10
  • @MadhurBhaiya I was writting the edit while you commented it. :-D Thanks – Elias Soares Oct 30 '18 at 17:11
  • Ah. OK, thanks for that. OK, thanks for the answer. Then I will use text as planned. I'm glad that the collation change from uft8 to uft8mb4 worked without any problems. Now I only have to adjust all the VARCHAR (191+) columns. @Madhur Bhaiya: No problem in my case, because I don't want to index these columns. – Norman Huth Oct 30 '18 at 17:16
  • So Yes, VARCHAR. Had only surprised me, because with the conversion an error message came with the whole VARCHAR (255) fields. Just tested it out. Could store 350 characters (letters, characters and numbers) in a VARCHAR column without any problems. – Norman Huth Oct 30 '18 at 17:22
  • @NormanHuth - What version do you have? You _may_ still have issues with 191. See [_this_](http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes) for 5 workaounds. _But_ the issues are with indexing, _not_ with `VARCHAR` vs `TEXT`. – Rick James Oct 30 '18 at 22:41
  • @Rick James: XAMPP: 10.1.34-MariaDB Live Server: 5.6.38-nmm1-log - (Ubuntu) – Norman Huth Oct 31 '18 at 19:09
  • 1
    @NormanHuth - MariaDB 10.1 and MySQL 5.6 have the `VARCHAR(191)` (aka 767-byte) index problem. See my link. – Rick James Nov 01 '18 at 01:41
0

If you want to store 250 characters in a column then use VARCHAR(250) or more. You can also use TEXT type. It really doesn't matter much. Both are variable length. I recommend using a much larger maximum size than you require. You do not lose anything by specifying more than you need.

Size of the column has nothing to do with encoding because MySQL counts the number of characters, not bytes. If you want to store all Unicode characters then you must use utf8mb4 charset.

Dharman
  • 30,962
  • 25
  • 85
  • 135