[back]

MySQL Data Types:

MySQL requires you to identify what type of data will be put into each feild. There are three primary types of data:

  • Text
  • Numbers
  • Dates and Time

For our exercises, we will be using the first two types.

The following table provides a basic description of fields that we are using. To get the entire list, go to the MySQL web site at http://www.mysql.com/doc/en/Column_types.html

CHAR[length] A fixed-length field from 0 to 255 characters long.
VARCHAR[length] A variable-length field from 0 to 255 characters long.
TEXT A string with a maximum length of 65,535 characters.
LONGTEXT A string with a maximum length of 4,294,967,295 characters.
INT[length] Range of -2,147,483,648 to 2,147,483,647

Clarifications:

`id` INT( 6 ) NOT NULL AUTO_INCREMENT ,
`last` VARCHAR( 20 ) NOT NULL ,
`first` VARCHAR( 20 ) NOT NULL ,
`grad_year` INT( 4 ) NOT NULL ,
`student_no` INT( 6 ) NOT NULL ,
PRIMARY KEY ( `id` )
)";

NULL / NOT NULL: By using these commands, you are telling the database that it is OK for a field to be empty or to require that the field not be empty. In our case, it is important that we have each piece of information. If we were asking for an email address, for example, we might assume that not everyone has one and therefore would allow the field to be empty. If you do not designate NULL or NOT NULL, the default is NULL.

VARCHAR vs CHAR: These two field types are very similar in that you must designate how many characters you will be using. The difference is that a VARCHAR field will only be as long as the input where CHAR will use spaces to pad any unused space.