relational databases and the applications built around relational databases. For a
long time MySQL didnâ€™t support transactions, and this absence was seen by many
as a fatal flaw. A lot of developers wouldnâ€™t go near MySQL because of it.
But that is no longer the case: MySQL does support full ACID transactions (see
Chapter 1 for the definition of ACID). But in order to make use of transactions you
need to use table types that support this feature. The following discussion of the
table types available in MySQL is extremely important. Make sure to read it care-
fully and keep up on changes to MySQL table types by checking the MySQL online
manual semi-regularly. If you have further questions about MySQL table types you
should consult the online manual for the latest information.
On most installations MyISAM is the default MySQL table type. A couple of gener-
ations back it was the only table type available in MySQL. MyISAM tables are
extremely fast and stable; however, they do not support transactions. They only
offer table-level locking of data.
MyISAM tables are optimized for speed in retrieving data with select state-
ments. Because of the optimization and lack of transaction support, MyISAM tables
are best for tables that are going to run select operations far more frequently than
they run update or delete operations.
For example, if you are creating a shopping cart (as we do in Chapter 14) you
likely have a table or two dedicated to the product catalog and other tables dedi-
cated to recording user information and orders. The tables that hold catalog infor-
mation (the items available in your store) probably wonâ€™t change all that
frequently â€” at most a couple of times a day. And if your store is doing well, these
data will be queried frequently, as users browse the items you have available.
MyISAM tables are perfect for tables that serve this purpose. The tables that store
shopping-cart data and record sales information are going to be subject of insert
and update queries far more frequently than they will be subject of select queries.
For these sorts of tables youâ€™re much better off using one of the transactional table
types: InnoDB, Gemini, or BerkeleyDB.
On almost all systems, MyISAM will be the default table type. Youâ€™ll be able to
run any valid create statement, and MySQL will create a MyISAM table, even if
40 Part I: Working with MySQL
you donâ€™t include a type attribute in your create statement. If you want to be extra
careful, however, you can include type=myisam in your statement, like so:
create table mytable(
InnoDB tables provide full ACID transaction support (see Chapter 1 for the defini-
tion of ACID) and row-level locking. Though other transactional table types are
available in MySQL, InnoDB is probably the transactional table that most readers of
this book will decide to use. MySQL AB (the company that maintains MySQL) pack-
ages InnoDB tables with its standard distribution and is working closely with
Innobase (www.innobase.com) to see that these tables work well with MySQL.
If youâ€™re hosting your application at an ISP, youâ€™ll want to make sure that the
host supports InnoDB tables before you write your applications for those tables.
You can check to see that these tables are available by running the following query:
show variables like â€˜have%â€™.
mysql> show variables like â€˜have%â€™;
| Variable_name | Value |
| have_bdb | NO |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
6 rows in set (0.30 sec)
As you can see from the preceding output, the value for have_innodb is YES. If
the value on your or your ISPâ€™s system is NO, InnoDB tables are not available.
To create InnoDB tables add type=innodb to your create statement, as follows:
create table mytable(
Chapter 2: The Structured Query Language for Creating and Altering Tables 41
In the applications presented in this book, we have chosen to implement
transactions using InnoDB tables. Even if you come to this book with a
strong background in relational databases, you will need to read Chapter 12,
where we discuss InnoDBâ€™s transactional model in detail.
BerkeleyDB tables come from Sleepycat software. This table type provides transac-
tion support but offers only page-level locking. While these tables are reasonably
good, thereâ€™s very little reason to use Berkeley tables when InnoDB tables are avail-
able. And at this point InnoDB tables are available to just about everyone.
Sleepycatâ€™s Web site is www.sleepycat.com.
Heap tables are actually memory-resident hash tables. They are not stored in any
physical location and therefore will disappear in case of a crash or power outage.
But because of their nature, they are blazingly fast. You should use these tables
only for temporary tables â€” but remember that all users can access heap tables.
The alter table Statement
If youâ€™re not happy with the form of your table, you can modify it with the alter
table statement. Specifically, this statement enables you to rename tables,
columns, and indexes; add or drop columns and indexes; and change the defini-
tions of columns and indexes. It also enables you to change tables from one type to
another (from MyISAM to InnoDB, for example). This statement always starts with
alter table table_name. The rest of the command depends on the action needed,
as described in the following sections.
Changing a table name
The syntax for changing a table name is as follows:
alter table table_name rename new_table_name
To rename a table named users to users_old, you would use the following
alter table users rename users_old;
42 Part I: Working with MySQL
If you have MySQL version 3.23.27 or higher you can make use of the
rename statement.The basic syntax of this statement is as follows:
rename table_name TO new_table_name
When adding a column, include all column definitions expected in the create
statement (column name, type, null|not null, default value, and so on). The basic
syntax is as follows:
alter table table_name add column column_name column_attributes
For example, to add a column to a table named users that stores a cell-phone
number, you could run the following command:
alter table users add column cell_phone varchar(14) not null;
In MySQL you can also specify the location of a column â€” that is, where in the
listing of columns it should appear (first, last, or before or after a specific column).
Use the word first at the end of your alter statement to place your inserted col-
umn as the first column in the table; use the phrase after column-name to place
the column after a column that already exists, as shown in the following examples.
So if you wanted to put the cell_phone column first in your users table, you
would use the following command:
alter table users add column cell_phone varchar(14) not null first;
If you wanted to place the cell_phone column between the home_phone and
work_phone columns, you would use the following:
alter table users add column cell_phone varchar(14) not null after
Donâ€™t spend a lot of time worrying about the order of your columns within a
table. One of the tenets of database design holds that column order is arbi-
trary. Any time the order of columns retrieved form the database is impor-
tant, you need to specify the column order in your query.
Chapter 2: The Structured Query Language for Creating and Altering Tables 43
To drop a column, you need only the following command:
alter table table_name drop column column_name
So to drop the cell_phone column, use this:
alter table users drop column cell_phone;
You can add indexes using the index, unique, and primary key commands in the
same way you would use them in the create statement:
alter table my_table add index index_name (column_name1, column_name2, ?)
alter table my_table add unique index_name(column_name)
alter table my_table add primary key(my_column)
For example, if you wanted to add an index on the email column of the users
table the following would do the trick:
alter table users add index index_on_email (email);
Making your indexes go away is easy enough with the drop command:
alter table table_name drop index index_name
To drop the index on the email column, use:
alter table users drop index index_on_email;
Changing column definitions
It is possible to change a columnâ€™s name or attributes with either the change or
modify command. To change a columnâ€™s name you must also redefine the columnâ€™s
attributes. The following will work:
alter table my_table change my_col2 my_col3 int not null;
But this will not:
alter table my_table change my_col2 my_col3;
44 Part I: Working with MySQL
If you wish to change only the columnâ€™s attributes, you can use the change com-
mand and make the new column name the same as the old column name. For
example, to change the lname column from a varchar(25) column to a char(25)
column, you can use the following:
alter table users change lname lname char(25);
Or you may prefer the modify command:
alter table users modify lname char(25);
When altering a table, try to get all of your changes into a single alter
statement and separate the different portions with commas. Itâ€™s better prac-
tice than, for example, deleting an index in one statement and creating a
new one in another statement. For example, the following statement would
run a single alter command on a table named users that modifies the
column type of lname and adds an index on the email column:
mysql> alter table users
-> modify lname char(25),
-> add index index_on_email(email);
Using the show Command
A series of commands in MySQL enables you examine the databases on your sys-
tem and lets you know what is available in your MySQL installation. Keep these
commands in mind, because they come in handy at times.
When you start your MySQL command line, you are connected to the MySQL server
but are initially given no indication as to what is available to the server.
shell> mysql -u root;
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 73 to server version: 3.23.39
Type â€˜help;â€™ or â€˜\hâ€™ for help. Type â€˜\câ€™ to clear the buffer.
Chapter 2: The Structured Query Language for Creating and Altering Tables 45
That prompt is nice but not especially helpful. Your initial interest is probably in
seeing what databases are available. You can get a list of databases by issuing the
show databases command:
mysql> show databases;
| Database |
| mysql |
| store |
| test |