String vs Text column types

Ruby
1

In a migration, I can create columns of various types, including :string or :text. What is the difference? Does type :string limit the number of characters used? It seems like I would be able to find a cheatsheet online explaining the difference, but I haven’t yet. Thanks.

2

string is varchar(255), text is text in MySQL

Create a table using both methods, then …
mysql> describe tablename;
… if you want to double-check. :slight_smile:

3

Here is a listing of MySQL field types. Other database will have variations to these (e.g. [URL=“http://www.postgresql.org/docs/7.4/interactive/datatype.html”]Postgres).

String is limited in size. By default it creates a varchar field of 255 maximum size as samsm indicates. You can also use the :limit option to reduce the size of the field further. So:

t.column "title", :string, :limit => 40

Will create a varchar field called “title” with a maximum string size of 40 characters.

A text field is far larger. For MySQL 65535 characters.

In general, the smaller the field size the more efficient the database. Therefore, if you used text fields for all your string fields, your database would be slower than if you used varchar fields where possible. So restrict using text to fields where you want to store large pieces of character data.

So for a blog the name of the author is unlikely to be greater that 100 characters, so a varchar field is best. Whereas the content of the blog is very likely to be of variable length and often greater than 255 characters. Use a text field for that.