<<

. 11
( 132 .)



>>

— int/integer

— tinyint

— mediumint

— bigint

— float

— double/double precision/real

— decimal/numeric

Be aware that real is synonymous with float when running in ANSI mode.


For all numeric types the maximum display size is 255. For most numeric
types you will have the option to zerofill a column ” to left-pad it with zeros.
For example, if you have an int column that has a display size of 10 and you
insert a value of 25 into this column, MySQL will store and display
0000000025.The numeric column types may also be defined as signed or
unsigned. signed is the default definition.
34 Part I: Working with MySQL

int/integer
Usage: int(display size) [unsigned] [zerofill]
If you use the unsigned flag, this column type can store integers from 0 to
4,294,967,295. If you use the signed flag, the range is from -2,147,483,648 to
2,147,483,647. int will often be used with auto_increment to define the primary
key of a table:

create table my_table (
table_id int unsigned auto_increment primary key,
next_column text
);

Note that we™ve used an unsigned column because an auto_increment column
has no need for negative values.

tinyint
Usage: tinyint(display size) [unsigned] [zerofill]
If unsigned, tinyint stores integers between 0 and 255. If signed, the range is
from -128 to 127.

mediumint
Usage: mediumint(display size) [unsigned] [zerofill]
If you use the unsigned flag, mediumint stores integers between -8,388,608 and
8,388,607. If you use the signed flag, the range is from 0 to 1677215.

bigint
Usage: bigint(display size) [unsigned] [zerofill]
If you use the signed flag, bigint stores integers between -9,223,372,036,854,
775,808 and 9,223,372,036,854,775,807. If you use the unsigned flag, the range is
from 0 to 18,446,744,073,709,551,615.

float
Float has two distinct usages.

— Usage: float(precision) [zerofill]

In this usage, float stores a floating-point number and cannot be
unsigned. The precision attribute can be ¤ 24 for a single-precision
floating-point number, and between 25 and 53 for a double-precision
floating-point number.
— Usage: float[(M,D)] [zerofill]

This is a small (single-precision) floating-point number and cannot be
unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38,
zero, and 1.175494351E-38 to 3.402823466E+38. M is the display width
and D is the number of decimals. If the float attribute is used without an
Chapter 2: The Structured Query Language for Creating and Altering Tables 35

argument or with an argument of ¤ 24, the column will store a single-
precision floating-point number.


double/double precision/real
Usage: double[(M,D)] [zerofill]
This column stores a double-precision floating-point number and cannot be
unsigned. Allowable values are -1.7976931348623157E+308 to -2.2250738585072
014E-308, zero, and 2.2250738585072014E-308 to 1.7976931348623157E+308. M
is the display width and D is the number of decimals.

decimal
Usage: decimal[(M[,D])] [zerofill]
Numbers in a decimal column are stored as characters. Each number is stored as a
string, with one character for each digit of the value. M is the display width, and D
is the number of decimals. If M is left out, it™s set to 10. If D is 0, values will have no
decimal point. The maximum range of decimal values is the same as for double.
Remember, though, that decimal, like all real types, can cause rounding errors.

Date and time types
MySQL provides you with five column types suitable for storing dates and times:

— date

— datetime

— timestamp

— time

— year

MySQL date and time types are flexible, accepting either strings or numbers as
part of insert statements. Additionally, MySQL is pretty good at interpreting dates
that you give it. For instance, if you create this table:

create table date_test(
id int unsigned auto_increment primary key,
the_date date
);

the following insert statements are all interpreted correctly by MySQL:

insert into date_test (a_date) values (˜00-06-01™);
insert into date_test (a_date) values (˜2000-06-01™);
insert into date_test (a_date) values (˜20000601™);
insert into test6 (a_date) values (000601);
36 Part I: Working with MySQL


MySQL prefers to receive dates as strings, so 000601 is a better choice than
a similar integer. Using strings for date values may save you from encounter-
ing some errors down the road.



Extracting information from date and time columns can be a challenge. MySQL
provides many functions that help manipulate these columns.

date
Usage: date
The date column type stores values in the format YYYY-MM-DD. It will allow val-
ues between 1000-01-01 and 9999-12-31.

datetime
Usage: datetime [null | not null] [default]
The datetime type stores values in the format YYYY-MM-DD HH:MM:SS. It will
allow values between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.

timestamp
Usage: timestamp(size)
This is a handy column type that will automatically record the time of the most
recent change to a row, whether from an insert or an update. Size can be defined
as any number between 2 and 14. Table 2-3 shows the values stored with each col-
umn size. The default value is 14. Bear in mind that if there are multiple
˜Timestamp™ fields, only the first will be automatically changed. A timestamp field
can later be forced to update by explicitly assigning it to NULL.


TABLE 2-3 timestamp FORMATS

Size Format

2 YY

4 YYMM

6 YYMMDD

8 YYYYMMDD

10 YYMMDDHHMM

12 YYMMDDHHMMSS

14 YYYYMMDDHHMMSS
Chapter 2: The Structured Query Language for Creating and Altering Tables 37

time
Usage: time
This type stores time in the format HH:MM:SS and has a value range from
-838:59:59 to 838:59:59. The reason for the large values is that the time column
type can be used to store the results of mathematical equations involving times.

year
Usage: year[(2|4)]
In these post-Y2K days it™s hard to imagine that you™d want to store your years
in two-digit format, but you can. In two-digit format, allowable dates are those
between 1970 and 2069, inclusive. The digits 70“99 are prefaced by 19, and 01“69
are by 20.
Four-digit“year format allows values from 1901 to 2155.



Creating Indexes
MySQL can create an index on any column. There can be a maximum of 16 indexed
columns for any standard table. (MyISAM tables support 32 indexes by default and
can be made to support 64.) The basic syntax is as follows:

index [index_name] (indexed_column)



Although the index name is optional, you should always name your indexes.
It becomes very important should you want to delete or change your index
using the SQL alter statement. If you don™t specify a name, MySQL will
base the index name on the first column in your index.



Another way to create an index is to declare a column as a primary key. Note
that any auto_increment column must be defined as part of a unique index and is
normally (but not necessarily) the primary key of the table. In the following code,
the id_col column is indexed:

create table my_table (
id_col int unsigned auto_increment primary key,
another_col text
);
38 Part I: Working with MySQL

The primary key can also be declared like other indexes, after the column defin-
itions, as in the following code:

create table my_table (
id_col int unsigned not null auto_increment,
another_col text,
primary key(id_col)
);

Indexes can span more than one row. If a query uses two rows in concert during
a search, you can create an index that covers the two with this statement:

create table mytable(
id_col int unsigned not null,
another_col char(200) not null,
index dual_col_index(id_col, another_col)
);

The preceding index will be used for searches that start on id_col and can
include another_col. Indexes of this kind work from left to right. So this index
will be used for searches that are exclusively on id_col. However, it will not be
used for searches on another_col.
You can also create indexes on only part of a column. For char, varchar, and
blob columns, you can create indexes for the initial portion of a column. Here the
syntax is as follows:

index index_name (column_name(column_length))

For example:

create table my_table(
char_column char (255) not null,
text_column text not null,
index index_on_char (char_column(20)),
index index_on_text (text_column(200))
);

An index can also assure that unique values exist in every row in a table by
using the unique constraint, as follows.

create table my_table(
char_column char (255) not null,
text_column text not null,
unique index index_on_char (char_column)
);
Chapter 2: The Structured Query Language for Creating and Altering Tables 39


Table Types
MySQL offers several table types: MyISAM, BDB, InnoDB, and Heap. The default
table type is MyISAM. The syntax for declaring a table type is as follows:

create table table_name (
column_name column_type column_attributes
)type=table_type

<<

. 11
( 132 .)



>>