MySQL VARCHAR Lengths and UTF-8

by

This answer showed up at the top of my google search results but wasn’t correct.

The confusion is probably due to different versions of MySQL being tested.

  • Version 4 counts bytes
  • Version 5 counts characters

Here is the quote from the official MySQL 5 documentation:

MySQL interprets length specifications in character column definitions in character units. (Before MySQL 4.1, column lengths were interpreted in bytes.) This applies to CHAR, VARCHAR, and the TEXT types.

Interestingly (I hadn’t thought about it) the max length of a varchar column is affected by utf8 as follows:

The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.

More Related Contents:

  1. Trouble with UTF-8 characters; what I see is not what I stored
  2. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  3. How to display UTF-8 characters in phpMyAdmin?
  4. How to make MySQL handle UTF-8 properly
  5. What is the difference between utf8mb4 and utf8 charsets in MySQL?
  6. How to conduct an Accent Sensitive search in MySql
  7. Fixing broken UTF-8 encoding
  8. UTF-8: General? Bin? Unicode?
  9. Python & MySql: Unicode and Encoding
  10. How to remove accents in MySQL?
  11. How to store unicode in MySQL?
  12. Equivalent of varchar(max) in MySQL?
  13. Storing and displaying unicode string (हिन्दी) using PHP and MySQL
  14. What are the differences between utf8_general_ci and utf8_unicode_ci? [duplicate]
  15. Incorrect string value: ‘\xF0\x9F\x8E\xB6\xF0\x9F…’ MySQL
  16. MySQL varchar index length
  17. How to detect UTF-8 characters in a Latin1 encoded column – MySQL
  18. Fetching UTF-8 text from MySQL in R returns “????”
  19. How to fix double-encoded UTF8 characters (in an utf-8 table)
  20. SQL unique varchar case sensitivity question
  21. Loading utf-8 encoded text into MySQL table
  22. Does MySQL Regexp support Unicode matching
  23. MySQL command line formatting with UTF8
  24. What to do when you need integers larger than 20 digits on mysql?
  25. MySQL matching unicode characters with ascii version
  26. Detecting utf8 broken characters in MySQL
  27. MySQL throws Incorrect string value error
  28. MySQL: Querying for unicode entities
  29. Mysql server does not support 4-byte encoded utf8 characters
  30. UTF8 problem with MySQL 5

Leave a Comment