<<

. 10
( 132 .)



>>


The use database Statement
Before you can begin making tables in MySQL you must select a database that
has been created. Though you can do this in individual SELECT statements, it™s eas-
ier to define a default working database with the use command. If you are access-
ing MySQL through the MySQL command-line client, you will have to enter this
statement:

use database_name

If you™re accessing a database through PHP, use the mysql_select_db()
function:

$conn = mysql_connect(“localhost”,”username”, “password”)
or die (“Could not connect to localhost”);

mysql_select_db(“test”, $conn) or
die (“Could not select database”);
28 Part I: Working with MySQL


The create table Statement
Once you have created and selected a database, you are ready to create a table. The
basic create table statement is fairly simple and takes this basic form:

create table table_name
(
column_name_1 column_type column_attributes,
column_name_2 column_type column_attributes,
primary key (column_name),
index index_name(column_name)
)type=table_type

Column types, column attributes, and details on indexes are covered in the fol-
lowing sections. Before we get to those, we should mention two simple column
attributes:

— null | not null

— default

The first gives you the opportunity to allow or forbid null values. If you don™t
specify null or not null it is assumed that null values are allowed. The second, if
declared, sets a value if none is declared when you insert a row into the table. (If a
column is defined as “not null” and no default value is specified, MySQL assigns
a default value for the column based on its data type. See the “CREATE TABLE
Syntax” section of the online MySQL Language Reference Manual for details.)
Here™s an example create statement that demonstrates these two attributes, and
a few others.

create table topics (
topic_id integer not null auto_increment,
parent_id integer default 0 not null,
root_id integer default 0,
name varchar(255),
description text null,
create_dt timestamp,
modify_dt timestamp,
author varchar(255) null,
author_host varchar(255) null,
primary key(topic_id),
index my_index(parent_id)
)type=myisam;
Chapter 2: The Structured Query Language for Creating and Altering Tables 29

This statement creates a table named topics with nine columns and two indexes,
one for the primary key and one for the parent_id column. In the preceding state-
ment four column types are used: integer, varchar, text, and timestamp. These
and many other column types are discussed in further detail in the following exam-
ple. Before you set out to create tables you should have a good understanding of all
the column types available as well as of ways to create indexes.
To create tables from the command-line client, key in the entire command. From
PHP, use the mysql_query() function as follows:

$conn = mysql_connect(“localhost”,”username”,”password”) or
die (“Could not connect to localhost”);

mysql_select_db(“test”, $conn) or
die(“could not select database”);
$query = “create table my_table (
col_1 int not null primary key,
col_2 text
)”;
mysql_query($query) or
die(mysql_error());




Column Types
MySQL provides you with a range of column types. While several are similar, sub-
tle yet important differences exist among them. Give this section a read and choose
carefully when deciding on column types for your tables.

String column types
Eight MySQL column types are suitable for storing text strings:

— char

— varchar

— tinytext/tinyblob

— text/blob
— mediumtext/mediumblob

— longtext/longblob

— enum

— set
30 Part I: Working with MySQL

char
Usage: char(length)
The char column type has a maximum length of 255 characters. This is a fixed-
length type, meaning that the field will be right-padded with spaces when a value
is inserted that has fewer characters than the maximum length of the column. So if
a column has been defined as char(10) and you want to store the value happy,
MySQL will actually store happy and then five spaces. The spaces are removed from
the result when the value is retrieved from the table. Values longer than the column
width are truncated.

varchar
Usage: varchar(length)
This type is nearly identical to char and is used in many of the same places. It
also has a maximum length of 255 characters. The difference is that varchar is a
variable-length column type, meaning that values will not be padded with spaces.
Instead MySQL will add one character to each varchar field to store the length of
the field.
MySQL removes spaces from the end of strings in varchar fields, but this behav-
ior might change in future releases of the language.


If you define a column as varchar with a column length of less than four,
MySQL will automatically change the column to the char type. Similarly, if
you try to mix chars and varchars with a column length of more than four,
they all become varchars.




Using char or varchar
For the most part, there is little practical difference between char and varchar.
Which one you decide to use will depend on which will require more space, the
trailing spaces in a char column or the size byte in varchar. If your field stores
something like last names, you™ll probably want to allow 25 characters, just to be safe.
If you were to use the char column type and someone had the last name Smith, your
column would contain 20 trailing spaces. There™s no need for that; you™re much better
off using varchar and allowing MySQL to track the size of the column. However,
when you want to store passwords of five to seven characters, it would be a waste to
use varchar to track the size of the column. Every time a varchar field is updated,
MySQL has to check the length of the field and change the character that stores the
field length. You™d be better off using char(7).
Chapter 2: The Structured Query Language for Creating and Altering Tables 31

tinytext/tinyblob
Usage: tinytext/tinyblob
These are the first of the four binary (or blob) column types. You can use
these columns to store both large strings and binary objects. Notice that we have
paired a text and a blob column here and in the following three listings. The only
difference between the paired items is the way in which MySQL will sort and com-
pare the values stored in the columns. For blob-column types (blob, tinyblob
mediumblob, largeblob), MySQL will perform case-sensitive comparisons. For
text-column types (tinytext, text, mediumtext, largetext), MySQL will perform
case-insensitive comparisons.
For example, if you were to make a table with the following create statement:

create table blob_test
(
blob_column text
);

and then insert a row with the following data into the table:

insert into blob_test (blob_column) values (˜THIS IS A STRING FOR MY BLOB™);


MySQL would run case-insensitive comparisons. Therefore, the following select
statement would return the inserted row:

mysql> select * from blob_test where blob_column like ˜this%™;
+---------------------------------+
| blob_column |
+---------------------------------+
| THIS IS A STRING FOR MY BLOB |
+---------------------------------+

If, however, the column were declared as a blob:

create table blob_test2
(
blob_column blob
);

and the same data were inserted, the previous select statement would not
match the row.
32 Part I: Working with MySQL


All of the statements used in this example will be explained in the remain-
der of Chapter 2 and in Chapter 3.




All of these types (tinytext/tinyblob, text/blob, mediumtext/mediumblob,
and largetext/largeblob) are variable column types, similar to varchar. They
differ only in the size of the string they can contain. The tinytext/tinyblob
type has a maximum length of 255, so in fact it serves the same purpose as
varchar(255). An index can be created for an entire tinytext column, but remem-
ber that tinytext and tinyblob fields preserve trailing whitespace characters.

text/blob
Usage: text/blob
The text/blob type has a maximum length of 65,535 characters.

mediumtext/mediumblob
Usage: mediumtext/mediumblob
The mediumtext/mediumblob type has a maximum length of 16,777,215
characters.

longtext
Usage: longtext/longblob
The longtext type has a maximum length of 4,294,967,295 characters. However,
this column currently is not very useful, as MySQL allows strings of only 16 million
bytes.

enum
Usage: enum (˜value1™, ˜value2™, ˜value3™ ?) [default ˜value™]
With enum, you can limit the potential values of a column to those you specify.
It allows for 65,535 values, though it™s difficult to imagine a situation in which
you™d want to use this column with more than a few potential values. This type is
of use when, for example, you want to allow only values of yes or no. The create
statement that makes use of enum will look like this:

create table my_table (
id int auto_increment primary key,
answer enum (˜yes™, ˜no™) default ˜no™
);



set
Usage: set (˜value1™, ˜value2™, ˜value3™ ?) [default ˜value™]
Chapter 2: The Structured Query Language for Creating and Altering Tables 33

This column type defines a superset of values. It allows for zero or more values
from the list you specify to be included in a field. You will not see this column type
used in this book: We do not like to see multiple values in a single field, as it vio-
lates very basic rules of database design. (Reread Chapter 1 if you don™t know what
we mean by this.) You can see an example of where set makes sense in the MySQL
grant tables, which are discussed in Appendix E.


Bear in mind that set columns can yield big savings in storage space and
improvements in performance as databases get bigger. Suppose you have
20,000,000,000 rows and you need to store the state of eight binary
switches for each row. To normalize this out would require a bigint and a
tinyint for each switch. Even without indexes you are looking at about
185GB for the table. Using a set column, you would require only 37.25GB for
this problem. However, this isn™t something you™ll see in this book.



Numeric column types
MySQL provides you with seven column types suitable for storing numeric
values. Note that the following are synonyms: int and integer; double, double
precision, and real; and decimal and numeric.

<<

. 10
( 132 .)



>>