81
230
Chapter 9 Creating Your Web Database
Understanding the Column Types
Let’s consider the first table as an example:
create table customers
( customerid int unsigned not null auto_increment primary key,
name char(50) not null,
address char(100) not null,
city char(30) not null
);
When creating any table,you need to make decisions about column types.
The
customers
table has four columns as specified in the schema.The first one,
customerid
,is the primary key,which is specified directly.We decided this will be an
integer (data type
int
) and that these IDs should be
unsigned
.We’ve also taken advan-
tage of the
auto_increment
facility so that MySQL can manage them for us;it’s one less
thing to worry about.
The other columns are all going to hold string type data.We chose the
char
type for
them.This type specifies fixed-width fields.The width is specified in the brackets,so,for
example,
name
can have up to 50 characters.
This data type will always allocate 50 characters of storage for the name, even if
they’re not all used.MySQL will pad the data with spaces to make it the right size.The
alternative is
varchar
,which uses only the amount of storage required (plus one byte).
There is a small trade-off:
varchar
s use less space on average,but
char
s are faster.
Note that all the columns are declared as
NOT NULL
.This is a minor optimization you
can make wherever possible that also will make things run a bit faster.We address opti-
mization in more detail in Chapter 12.
Some of the other
CREATE
statements have variations in syntax.Let’s look at the
orders
table:
create table orders
( orderid int unsigned not null auto_increment primary key,
customerid int unsigned not null,
amount float(6,2) ,
date date not null
);
The
amount
column is specified as a floating-point number of type
float
.With most
floating-point data types,you can specify the display width and the number of decimal
places.In this case,the order amount will be in dollars,so we allowed a reasonably large
order total (width 6) and two decimal places for the cents.
The
date
column has the data type
date
.
This particular table specifies that all columns bar the amount as
NOT NULL
.Why?
When an order is entered into the database, you need to create it in orders,add the
items to
order_items
,and then work out the amount.You might not know the amount
when the order is created,so you can allow for it to be
NULL
.
59
231
Creating Database Tables
The
books
table has some similar characteristics:
create table books
( isbn char(13) not null primary key,
author char(50),
title char(100),
price float(4,2)
);
In this case,you don’t need to generate the primary key because ISBNs are generated
elsewhere.The other fields are left as
NULL
because a bookstore might know the ISBN of
a book before it knows the
title
,
author
,or
price
.
The
order_items
table demonstrates how to create multicolumn primary keys:
create table order_items
( orderid int unsigned not null,
isbn char(13) not null,
quantity tinyint unsigned,
primary key (orderid, isbn)
);
This table specifies the quantity of a particular book as a
TINYINT UNSIGNED
,which
holds an integer between 0 and 255.
As mentioned previously,multicolumn primary keys need to be specified with a spe-
cial primary key clause.This clause is used here.
Lastly,consider the
book_reviews
table:
create table book_reviews
(
isbn char(13) not null primary key,
review text
);
This table uses a new data type,
text
,which we have not yet discussed. It is used for
longer text,such as an article.There are a few variants on this,which we discuss later in
this chapter.
To understand creating tables in more detail,let’s discuss column names and identifiers
in general and then the data types we can choose for columns.First,though,let’s look at
the database we’ve created.
Looking at the Database with SHOW and DESCRIBE
Log in to the MySQL monitor and use the
books
database.You can view the tables in
the database by typing
mysql> show tables;
46
232
Chapter 9 Creating Your Web Database
MySQL then displays a list of all the tables in the database:
+-----------------+
| Tables in books |
+-----------------+
| book_reviews |
| books |
| customers |
| order_items |
| orders |
+-----------------+
5 rows in set (0.06 sec)
You can also use
show
to see a list of databases by typing
mysql> show databases;
If you do not have the
SHOW DATABASES
privilege,you will see listed only the databases
on which you have privileges.
You can see more information about a particular table,for example,
books
,using
DESCRIBE
:
mysql> describe books;
MySQL then displays the information you supplied when creating the database:
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| isbn | char(13) | | PRI | | |
| author | char(50) | YES | | NULL | |
| title | char(100) | YES | | NULL | |
| price | float(4,2) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
These commands are useful to remind yourself of a column type or to navigate a data-
base that you didn’t create.
Creating Indexes
We briefly mentioned indexes already,because designating primary keys creates indexes
on those columns.
One common problem faced by new MySQL users is that they complain about poor
performance from this database they have heard is lightning fast.This performance prob-
lem occurs because they have not created any indexes on their database. (It is possible to
create tables with no primary keys or indexes.)
50
233
Understanding MySQL Identifiers
To begin with,the indexes that were created automatically for you will do.If you find
that you are running many queries on a column that is not a key,you may want to add
an index on that column to improve performance.You can do this with the
CREATE
INDEX
statement.The general form of this statement is
CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON table_name (index_column_name [(length)] [ASC|DESC], ...])
(
FULLTEXT
indexes are for indexing text fields;we discuss their use in Chapter 13,
“Advanced MySQL Programming.”)
The optional
length
field allows you to specify that only the first
length
characters
of the field will be indexed.You can also specify that an index should be ascending (
ASC
)
or descending (
DESC
);the default is ascending.
A Note on Table Types
You may be aware that MySQL offers more than one table type or storage engine,
including some transaction-safe types.We discuss the table types in Chapter 13.At pres-
ent,all the tables in the database use the default storage engine,MyISAM.
Understanding MySQL Identifiers
Five kinds of identifiers are used in MySQL:databases,tables,columns,and indexes,
which you’re already familiar with;and aliases, which we cover in the next chapter.
Databases in MySQL map to directories in the underlying file structure,and tables
map to files.This mapping has a direct effect on the names you can give them.It also
affects the case sensitivity of these names: If directory and filenames are case sensitive in
your operating system,database and table names will be case sensitive (for example,in
Unix);otherwise,they won’t (for example,under Windows).Column names and alias
names are not case sensitive,but you can’t use versions of different cases in the same
SQL statement.
As a side note,the location of the directory and files containing the data is wherever
it was set in configuration.You can check the location on your system by using the
mysqladmin
facility as follows:
mysqladmin variables
Then look for the
datadir
variable.
A summary of possible identifiers is shown in Table 9.4.The only additional excep-
tion is that you cannot use ASCII(0),ASCII(255),or the quoting character in identifiers
(and to be honest, we’re not sure why you would want to).
71
234
Chapter 9 Creating Your Web Database
Table 9.4 MySQL Identifiers
Type
Max
Case
Characters
Length
Sensitive?
Allowed
Database
64
same as OS
Anything allowed in a directory name in your
OS except the
/
,
\
,and
.
characters
Table
64
same as OS
Anything allowed in a filename in your OS
except the
/
and
.
characters
Column
64
no
Anything
Index
64
no
Anything
Alias
255
no
Anything
These rules are extremely open.
Since MySQL 3.23.6, you can even have reserved words and special characters of all
kinds in identifiers.The only limitation is that if you use anything unusual like this,you
have to put it in backticks (located under the tilde key on the top left of most key-
boards).For example,
create database `create database`;
The rules in versions of MySQL (prior to 3.23.6) are more restrictive and don’t allow
you to do this.
Of course,you should apply common sense to all this freedom.Just because you can
call a database
`create database`
doesn’t that mean that you should.The same princi-
ple applies here as in any other kind of programming: Use meaningful identifiers.
Choosing Column Data Types
The three basic column types in MySQL are numeric, date and time,and string.Within
each of these categories are a large number of types.We summarize them here and go
into more detail about the strengths and weaknesses of each in Chapter 12.
Each of the three types comes in various storage sizes.When you are choosing a col-
umn type,the principle is generally to choose the smallest type that your data will fit
into.
For many data types, when you are creating a column of that type,you can specify
the maximum display length.This is shown in the following tables of data types as
M
.If
it’s optional for that type,it is shown in square brackets.The maximum value you can
specify for
M
is 255.
Optional values throughout these descriptions are shown in square brackets.
86
235
Choosing Column Data Types
Numeric Types
The numeric types are either integers or floating-point numbers.For the floating-point
numbers,you can specify the number of digits after the decimal place.This value is
shown in this book as
D
.The maximum value you can specify for
D
is 30 or
M-2
(that is,
the maximum display length minus two—one character for a decimal point and one for
the integral part of the number),whichever is lower.
For integer types,you can also specify whether you want them to be
UNSIGNED
,as
shown in Listing 9.1.
For all numeric types, you can also specify the
ZEROFILL
attribute.When values from
a
ZEROFILL
column are displayed,they are padded with leading zeros.If you specify a
column as
ZEROFILL
,it will automatically also be
UNSIGNED
.
The integral types are shown in Table 9.5.Note that the ranges listed in this table
show the signed range on one line and the unsigned range on the next.
Table 9.5 Integral Data Types
Type
Range
Storage
Description
(Bytes)
TINYINT[(M)]
–127..128
1
Very small integers
or 0..255
BIT
Synonym for
TINYINT
BOOL
Synonym for
TINYINT
SMALLINT[(M)]
–32768..32767
2
Small integers
or 0..65535
MEDIUMINT[(M)]
–8388608..
3
Medium-sized integers
8388607
or 0..16777215
INT[(M)]
–231..231 –1
4
Regular integers
or 0..232 –1
INTEGER[(M)]
Synonym for
INT
BIGINT[(M)]
–263..263 –1
8
Big integers
or 0..264 –1
The floating-point types are shown in Table 9.6.
Table 9.6 Floating-Point Data Types
Type
Range
Storage
Description
(bytes)
FLOAT(precision)
Depends on
Varies
Can be used to specify
precision
single or double precision
floating-point numbers.
81
236
Chapter 9 Creating Your Web Database
FLOAT[(M,D)]
±1.175494351E-38
4
Single precision floating-
±3.402823466E+38
point number.These
numbers are equivalent to
FLOAT(4)
but with a
specified display width
and number of decimal
places.
DOUBLE[(M,D)]
±1.
8
Double precision floating-
7976931348623157E
point number.These
+308
numbers are equivalent
±2.2250738585072014E
to
FLOAT(8)
but with a
-308
specified display width
and number of decimal
places.
DOUBLE
Synonym for
PRECISION[(M,D)]
As above
DOUBLE[(M, D)]
.
REAL[(M,D)]
As above
Synonym for
DOUBLE[(M, D)]
.
DECIMAL[(M[,D])]
Varies
M+2
Floating-point number
stored as
char
.The range
depends on
M
,the display
width.
NUMERIC[(M,D)]
As above
Synonym for
DECIMAL
.
DEC[(M,D)]
As above
Synonym for
DECIMAL
.
FIXED[(M,D)]
As above
Synonym for
DECIMAL
.
Date and Time Types
MySQL supports a number of date and time types;they are shown in Table 9.7.With all
these types,you can input data in either a string or numerical format. It is worth noting
that a
TIMESTAMP
column in a particular row will be set to the date and time of the most
recent operation on that row if you don’t set it manually.This feature is useful for trans-
action recording.
Table 9.6 Continued
Type
Range
Storage
Description
(bytes)
Documents you may be interested
Documents you may be interested
