A couple of quick mysql questions

regarding field types and sizes

[HelenDev]

I'm getting back into mysql/php development after a bit of a break from it, and while setting up a new table in the database a couple of queries crossed my mind...

If you don't specify a field length does it just default to the longest value possible?

What is the benefit of using TEXT over say, MEDIUMTEXT or LONGTEXT? If you want a big-ish text field why not always use longtext if it's just the same but can hold more data?

[coopster]

Default value to place in that column? Or do you mean "default" column definition? I think you are referring to the latter but keep in mind that DEFAULT values versus default column definition are two different animals. You may be best served by reading through the MySQL Data Types [dev.mysql.com].

For example, if you were to

CREATE TABLE table (string CHAR, number INTEGER);

and then

DESCRIBE table;

you would note that the column definition for the 'string' column turns up a CHAR(1) value, which is expected because the way this string type [dev.mysql.com] was defined is a synonym for CHAR(1). The 'number' column definition takes on it's respective default as defined in the SQL standard. MySQL has extended that standard by allowing us to optionally specify the display width of integer data types in parentheses following the base keyword for the type.

The manual pages listed here should be a good starting point and help you understand how default column definitions are specifically defined and handled in MySQL. Personally, I like to analyze and design more specifically, leaving SMALLINT AND INTEGER values as SQL standard, as well as any others that don't require a specific column length.