| Setting Field Name and Data Type
Field Name
The Field Name is a descriptive identifier for a field that can be up to
64 characters (letters or numbers) including spaces. The names should be descriptive
enough that anyone can easily identify them when viewing or editing records.
For example, LastName, FirstName, StreetAddress, or HomePhone.
Use the Name edit box to set the field name. Note that the name of
the field must be unique among all the field names in the table.
Data Type
After you name a field, you choose a data type for the data to be contained
in the field. When you choose a field's data type, you are deciding:
- What kind of values to allow in the field. You cannot store text in field
with the Numeric data type.
- How much storage space MySQL is to set aside for the data in that field.
- What types of operations can be performed on the values in that field.
The Type dropdown list defines the type of the field data.
| Data Type |
Size |
| CHAR |
A fixed section from 0 to 255 characters long |
| VARCHAR |
A variable section from 0 to 255 characters long before
MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions |
| BINARY (binary strings) |
A fixed section from 0 to 255 bytes |
| VARBINARY (binary strings) |
A variable section from 0 to 255 bytes before
MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions |
| TINYTEXT |
A string with a maximum length of 255 characters |
| TEXT |
A string with a maximum length of 65535 characters |
| MEDIUMTEXT |
A string with a maximum length of 16777215 characters |
| LONGTEXT |
A string with a maximum length of 4294967295 characters |
above,
more
| Data Type |
Byte(s) |
Signed Range |
Unsigned Range |
| BIT |
As of MySQL 5.0.3, a BIT data type is available for storing bit-field
values. (Before 5.0.3, MySQL interprets BIT as TINYINT(1).) In MySQL
5.0.3, BIT is supported only for MyISAM. MySQL 5.0.5 extends BIT support
to MEMORY, InnoDB, and BDB
A type of BIT(M) allows for storage of M-bit values. M can range from
1 to 64
|
| TINYINT |
1 |
-128 to 127 |
0 to 255 |
| SMALLINT |
2 |
-32768 to 32767 |
0 to 65535 |
| MEDIUMINT |
3 |
-8388608 to 8388607 |
0 to 16777215 |
| INT |
4 |
-2147483648 to 2147483647 |
0 to 4294967295 |
| INTEGER |
This is a synonym for INT |
| BIGINT |
8 |
-9223372036854775808 to 9223372036854775807 |
0 to 18446744073709551615 |
above,
more
| Data Type |
Signed Range |
Unsigned Range |
| FLOAT |
-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38
to 3.402823466E+38 |
Negative values are disallowed |
| DOUBLE |
-1.7976931348623157E+308 to -2.2250738585072014E-308,
0, and 2.2250738585072014E-308 to 1.7976931348623157E+308 |
| REAL |
These are synonyms for DOUBLE. Exception: If the server
SQL mode includes the REAL_AS_FLOAT option, REAL is a synonym for FLOAT
rather than DOUBLE |
|
DECIMAL
|
In MySQL 5.03 and later:
They are stored in binary format. Range from -999.99 to 999.99
Before MySQL 5.0.3:
They are stored as strings. On the positive end of the range - store
numbers up to 9999.99
Before MySQL 3.23:
-9.99 to 99.99
|
| NUMERIC |
above,
more
| Data Type |
Supported Range |
Format |
| DATE |
'1000-01-01' to '9999-12-31' |
YYYY-MM-DD |
| DATETIME |
'1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
YYYY-MM-DD HH:MM:SS |
| TIMESTAMP |
'1970-01-01 00:00:00' to partway through the year 2037. |
In MySQL 4.0 and earlier:
YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD
From MySQL 4.1 on:
'YYYY-MM-DD HH:MM:SS'
|
| TIME |
'-838:59:59' to '838:59:59' |
HH:MM:SS |
| YEAR |
Four-digit format:
1901 to 2155, and 0000
Two-digit format:
70 to 69, representing years from 1970 to 2069
|
YYYY |
above,
more
| Data Type |
Size |
| TINYBLOB |
A string with a maximum length of 255 characters |
| BLOB |
A string with a maximum length of 65535 characters |
| MEDIUMBLOB |
A string with a maximum length of 16777215 characters |
| LONGBLOB |
A string with a maximum length of 4294967295 characters |
above,
more
| Data Type |
Values/Members |
Format |
| ENUM |
Maximum of 65,535 distinct values |
An enumeration. A string object that can have only one
value, chosen from the list of values 'value1', 'value2', ..., NULL
or the special '' error value |
| SET |
Maximum of 64 members |
A string object that can have zero or more values, each
of which must be chosen from the list of values 'value1', 'value2',
... |
above,
more
|
home
previous
