Are there any limits on length of string in mysql?

I am using MySQL data base with Rails. I have created a field of type string. Are there any limits to its length? What about type text?
Also as text is variable sized, I believe there would be extra costs associated with using text objects. How important can they get, if at all?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

CHAR

A fixed-length string that is always right-padded with spaces to the specified length when stored The range of Length is 1 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion according to the default character set unless the BINARY keyword is given.

VARCHAR

A variable-length string. Note: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification)
The range of Length is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given

TINYBLOB, TINYTEXT

A TINYBLOB or TINYTEXT column with a maximum length of 255 (2⁸ – 1) characters

BLOB, TEXT

A BLOB or TEXT column with a maximum length of 65,535 (2¹⁶ – 1) characters , bytes = 64 KiB

MEDIUMBLOB, MEDIUMTEXT

A MEDIUMBLOB or MEDIUMTEXT column with a maximum length of 16,777,215 (2²⁴ – 1)characters , bytes = 16 MiB

LONGBLOB, LONGTEXT

A LONGBLOB or LONGTEXT column with a maximum length of 4,294,967,295 (2³² – 1) characters , bytes = 4 GiB

See MySQL Data Types Quick Reference Table for more info.

also you can see MYSQL – String Type Overview

Method 2

String, in general, should be used for short text. For example, it is a VARCHAR(255) under MySQL.

Text uses the larger text from the database, like, in MySQL, the type TEXT.

For information on how this works and the internals in MySQL and limits and such, see the other answer by Pekka.

If you are requesting, say, a paragraph, I would use text. If you are requesting a username or email, use string.

Method 3

See the mySQL manual on String Types.

Varchar (String):

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. 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.

Text: See storage requirements

If you want a fixed size text field, use CHAR which can be 255 characters in length maximum. VARCHAR and TEXT both have variable length.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0