Not Your Type? - MySQL

The SQL command used to create a new table in a database typically looks like this:

---

CREATE TABLE <table_name> (<field_name_1> <field_type_1> <modifiers>,
<field_name_2> <field_type_2> <modifiers>, ... , <field_name_n>
<field_type_n> <modifiers>)

---

The table name cannot contain spaces, slashes or periods; other than this, anything is fair game. Each table (and the data it contains) is stored as a set of three files in your main MySQL directory.

Here's a sample command to create the "members" table in the example you saw a few pages back:

---

mysql> CREATE TABLE members (member_id int(11) NOT NULL auto_increment,
fname varchar(50) NOT NULL, lname varchar(50) NOT NULL, tel varchar(15),
email varchar(50) NOT NULL, PRIMARY KEY (member_id));
Query OK, 0 rows affected (0.05 sec)

---

Note that each field name is followed by a "type" - this identifies the type of data that will be allowed in that field. MySQL offers a number of different data types, some of which are summarized below:

INT - a numeric type which can accept values in the range of -2147483648 to 2147483647

For numeric fields like INT, you can optionally specify the size of the field in parentheses after the field type; MySQL will automatically "pad" values which are shorter than the specified width with spaces. If you add the optional ZEROFILL modifier, these spaces are replaced with zeroes. You can also restrict entries to positive values only with the UNSIGNED modifier.

For example, INT(15) UNSIGNED ZEROFILL would create an INT field which only allowed positive values and padded values containing less than fifteen digits with zeroes.

DECIMAL - a numeric type with support for floating-point or decimal numbers

You can specify the number of digits displayed both before and after the decimal point in parentheses. For example, DECIMAL (6,3) would create a numeric type with support for values in the range -999.999 to 999.999

DOUBLE - a numeric type for double-precision floating-point numbers. If you don't know what these are, chances are you won't be using it much.

DATE - a date field in the YYYY-MM-DD format

TIME - a time field in the HH:MM:SS format

DATETIME - a combined date/time type in the YYYY-MM-DD HH:MM:SS format

YEAR - a field specifically for year displays in the range 1901 to 2155, in either YYYY or YY formats

TIMESTAMP - a timestamp type, in YYYYMMDDHHMMSS format

The TIMESTAMP type is particularly handy for situations where you would like to record the date and time of an entry into the table. This field is automatically set to the current date and time if no value is specified when inserting records, or if the value is specified as NOW().

You can specify a size for the TIMESTAMP field, depending on whether you would like a "full" or "abbreviated" timestamp.

CHAR - a string type with a maximum size of 255 characters and a fixed length

VARCHAR - a string type with a maximum size of 255 characters and a variable length

You can specify the size of a CHAR and VARCHAR field in parentheses. If values entered are longer than the specified size, they are automatically truncated.

The difference between CHAR and VARCHAR is that CHAR values are always "fixed" to a specific size, regardless of the number of characters entered. For example, CHAR(200) would create a character field which would "pad" values with spaces to a size of 200 characters. In contrast, VARCHAR values are stored as they are entered, with no additional padding.

TEXT - a string type with a maximum size of 65535 characters

BLOB - a binary type for variable data

ENUM - a string type which can accept one value from a list of previously-defined possible values

For example, ENUM("huey", "dewey", "louie") would create an ENUM field which could contain either of the specified three values, or a null value.

An ENUM type can have up to 65535 elements.

SET - a string type which can accept zero or more values from a set of previously-defined possible values

For example, SET("huey", "dewey", "louie") would create a SET field which could contain one of more of the specified three values, or a null value. A SET type can have up to 64 elementsts

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus