Working with Database Structures - Learning MySQL - page 188

PRIMARY KEY (`artist_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------+
1 row in set (0.08 sec)
We've reformatted the output slightly to fit better in this topic. You'll notice that the
output includes content added by MySQL that wasn't in our original CREATE TABLE
statement:
• The names of the table and columns are enclosed in backticks. This isn't necessary,
but it does avoid any parsing problems that can occur through using reserved words
and special characters, as discussed previously
• An additional default ENGINE clause is included, which explicitly states the table
type that should be used. The setting in a default installation of MySQL is MyI
SAM , so it has no effect in this example
• An additional DEFAULT CHARSET=latin1 clause is included, which tells MySQL what
character set is used by the columns in the table. Again, this has no effect in a
default, Latin-character-set-based installation
Column Types
This section describes the column types you can use in MySQL. It explains when each
should be used and any limitations it has. We've ordered the choices in two sections:
first, the commonly used, and, second, the less frequently used choices. Skip the second
part if you want to and revisit it when one of the common choices doesn't fit your needs;
it's certainly worth reviewing when you're tackling the exercises at the end of this
chapter.
Common column types
The following are the six commonly used column types in MySQL tables:
INT[( width )] [UNSIGNED] [ZEROFILL]
The most commonly used numeric type. Stores integer (whole number) values in
the range -2,147,483,648 to 2,147,483,647. If the optional UNSIGNED keyword is
added, the range is 0 to 4,294,967,295. The keyword INT is short for INTEGER , and
they can be used interchangeably. An INT column requires four bytes of storage
space.
You can also include optional width and ZEROFILL arguments to left-pad the values
with zeros up to the specified length. The maximum width is 255. The width pa-
rameter has no effect on what is stored. If you store a value wider than the width ,
the width value is ignored. Consider this example:
mysql> CREATE TABLE numbers (my_number INT(4) ZEROFILL );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO numbers VALUES(3),(33),(333),(3333),(33333),(333333);