Questions about types in MySQL

So I was creating a table for comments, and I was wondering. What would be a good type for comment details? I put longtext. Well then, why would people need varchar if longtext can handle it? Also, which type would I want for usernames?

What is the purpose of "primary" for index? What is the开发者_如何学JAVA purpose of index?

Update: Let's say a comment was actually a review.

It is true that TEXT can handle any input you'd place in VARCHAR or CHAR field. In fact TEXT could handle and data you might want to put in DECIMAL, INT, or almost any other type as well. Following this logic we might as well make every column a TEXT type.

But this would be a mistake. Why? Because using the appropriate column type for the expected input allows the database to better optimize queries, uses less disk space and makes the data model easier to understand and maintain.

In regards to the questions: a username column should use VARCHAR(20), since you would want and expect that most usernames are going to short, usually no more than 10 - 20 characters long. For a review column (like a movie review or book review) a TEXT type would be appropriate as reviews can span a single paragraph to several pages.

In regards to indexes, try this link:

http://20bits.com/articles/interview-questions-database-indexes/

That depends on what a "comment" is in your system. Typically VARCHAR is pretty standard for both comments and usernames. This limits you to about 255 characters, which is generally pretty acceptable. If you need more characters in your comments, you can bump it up to a text, which gives you a little over 65k chars.

For more information, see the String Types Reference.

TEXT NOT NULL. That gives sufficient room, has a 2 byte overhead, and generally presents no problems.

Regarding TEXT

On comparisons, TEXT is space extended to fit the compared object, exactly like CHAR and VARCHAR.

For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 7.4.2, “Column Indexes”.

BLOB and TEXT columns cannot have DEFAULT values.

If you use the BINARY attribute with a TEXT data type, the column is assigned the binary collation of the column character set.

Regarding VARCHAR:

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.

More at: http://dev.mysql.com/doc/refman/5.0/en/blob.html

Have a look at this web page, it lists all the MySQL field types and describes what they are and how they're different from each other.