The purpose, advantages, disadvantages, and relevant configuration parameters of MySQL’s key storage engines, namely ARCHIVE, BLACKHOLE, CSV, EXAMPLE, FEDERATED, InnoDB, MEMORY (formerly HEAP), MERGE, and MyISAM.
The purpose and range of MySQL’s supported data types. To facilitate later reference, these data types are broken into three categories: date and time, numeric, and textual.
MySQL’s table attributes, which serve to further modify the behavior of a data column.
The MySQL commands used to create, modify, navigate, review, and alter both databases and tables.
Storage Engines
A relational database table is a data structure used to store and organize information. You can picture a table as a grid consisting of both rows and columns, much like a spreadsheet. For example, you might design a table intended to store employee contact information, and that table might consist of five columns: employee ID, first name, last name, e-mail address, and phone number. For an organization that consists of four employees, this table would consist of four rows, or records. Although this example is simplistic, it clearly depicts the purpose of a table: to serve as an easily accessible vehicle for general data storage.
However, database tables are also used in a number of other ways, some of which are rather complex. For example, databases are also commonly used to store transactional information. A transaction is a group of tasks that is collectively considered to be a single unit of work. If all the unit tasks succeed, then the table changes will be executed, or committed. If any task fails, then all the results of the preceding and proceeding tasks must be annulled, or rolled back. You might use transactions for procedures such as user registration, banking operations, or e-commerce, in which all steps must be correctly carried out to ensure data consistency. As you might imagine, such capabilities require some overhead due to the additional features that must be incorporated into the table.
Note
MySQL’s transactional features are introduced in Chapter 34.
InnoDB
MyISAM
MEMORY
MERGE
FEDERATED
ARCHIVE
CSV
EXAMPLE
BLACKHOLE
Following the presentation of the storage engines is an FAQ section to address other issues regarding storage engines.
InnoDB
InnoDB is a robust transactional storage engine released under the GNU General Public License (GPL) that has been under active development for over a decade. InnoDB offers users a powerful solution for working with very large data stores. It has been available to MySQL users since version 3.23.34a and has proved such a popular and effective solution for transactional applications that support has been enabled by default since version 4.0.
Although InnoDB is commonly grouped with other storage engines, as is done here, it’s actually a complete database back end unto itself. InnoDB table resources are managed using dedicated buffers, which can be controlled like any other MySQL configuration parameters. InnoDB also brings other great advances to MySQL by way of row-level locking and foreign key constraints.
Update-intensive tables: The InnoDB storage engine is particularly adept at handling multiple simultaneous update requests.
Transactions: The InnoDB storage engine is the only standard MySQL storage engine that supports transactions, a requisite feature for managing sensitive data such as financial or user registration information.
Automated crash recovery: Unlike other storage engines, InnoDB tables are capable of automatically recovering from a crash. Although MyISAM tables can also be repaired after a crash, the process can take significantly longer. A crash safe version of MyISAM called Aria is also available.
MyISAM
MyISAM used to be MySQL’s default storage engine. It resolved a number of deficiencies suffered by its predecessor (ISAM). For starters, MyISAM tables are operating system independent, meaning that you can easily port them from a Windows server to a Linux server. In addition, MyISAM tables are typically capable of storing more data, but at a cost of less storage space than their older counterpart. MyISAM tables also have the convenience of a number of data integrity and compression tools at their disposal, all of which are bundled with MySQL.
Select-intensive tables: The MyISAM storage engine is quite fast at sifting through large amounts of data, even in a high-traffic environment.
Append-intensive tables: MyISAM’s concurrent insert feature allows for data to be selected and inserted simultaneously. For example, the MyISAM storage engine would be a great candidate for managing mail or web server log data.
MyISAM Static
Inserting just this single row into each will result in authentication_static being a little over 60 percent larger than authentication_dynamic (33 bytes versus 20 bytes), because the static table always consumes the space specified within the table definition, whereas the dynamic table only consumes the space required of the inserted data. However, don’t take this example as a ringing endorsement for adhering solely to the MyISAM-dynamic format. The following section discusses this storage engine’s characteristics, including its disadvantages.
MyISAM Dynamic
Use static data types whenever possible.
Use the OPTIMIZE TABLE statement on a regular basis, which defragments tables and recovers space lost over time due to table updates and deletions.
MyISAM Compressed
Sometimes you’ll create tables that are intended as read-only throughout the lifetime of your application. If this is the case, you can significantly reduce their size by converting them into MyISAM-compressed tables using the myisampack utility. Given certain hardware configurations (a fast processor and slow hard drive, for example), performance savings could be significant.
MEMORY
MySQL’s MEMORY storage engine was created with one goal in mind: speed. To attain the fastest response time possible, the logical storage media is system memory. Although storing table data in memory does indeed offer impressive performance, keep in mind that if the MySQL daemon crashes, all MEMORY data will be lost.
Note
As of version 4.1, this storage engine was renamed from HEAP to MEMORY. However, because this storage engine has long been a part of MySQL, you’ll still see it commonly referred to by its old name in documentation. Additionally, HEAP remains a synonym of MEMORY.
Negligible: The target data is relatively small compared to the available system memory in size and accessed very frequently. Remember that storing data in memory prevents that memory from being used for other purposes. Note that you can control the size of MEMORY tables with the parameter max_heap_table_size . This parameter acts as a resource safeguard, placing a maximum limit on the size of a MEMORY table.
Transient: The target data is only temporarily required, and during its lifetime must be made immediately available.
Relatively inconsequential: The sudden loss of data stored in MEMORY tables would not have any substantial negative effect on application services, and certainly should not have a long-term impact on data integrity.
Both hashed and B-tree indexes are supported. The advantage of B-tree indexes over hashes is that partial and wildcard queries can be used, and operators such as <, >, and >= can be used to facilitate data mining.
MERGE
MyISAM also offers an additional variant that isn’t as prominently used as the others, but is nonetheless quite useful in certain situations. This variant, known as a MERGE table, is actually an aggregation of identical MyISAM tables. Why is this useful? Consider that databases are often used for storing time-specific data: sales information, server logs, and flight timetables all immediately come to mind as prime candidates. Such data stores, however, can easily become excessively large and quite unwieldy. As a result, a common storage strategy is to break the data up into numerous tables, with each name pertinent to a particular time block. For example, 12 identical tables might be used to store server log data, with each assigned a name corresponding to each month of the year. However, reports based on data spread across all 12 tables are necessary, meaning multitable queries will need to be written and updated to reflect the information found within these tables. Rather than write such potentially error-prone queries, the tables can be merged together and a single query can be used instead. The MERGE table can later be dropped without affecting the original data.
FEDERATED
Many environments tend to run Apache, MySQL, and PHP on a single server. Indeed, this is fine for many purposes, but what if you need to aggregate data from a number of different MySQL servers, some of which reside outside the network or are owned by another organization altogether? Because it’s long been possible to connect to a remote MySQL database server (see Chapter 24 for more details), this doesn’t really present a problem; however, the process of managing connections to each separate server can quickly become tedious. To alleviate this problem, you can create a local pointer to remote tables by using the FEDERATED storage engine, available as of MySQL 5.0.3. Doing so allows you to execute queries as if the tables reside locally, saving the hassle of separately connecting to each remote database.
Note
The FEDERATED storage engine isn’t installed by default, so you need to configure MySQL with the option --with-federated-storage-engine to take advantage of its features. In addition, the MySQL server must be started with the --federated option.
The connection string should be fairly easy to understand, but a few observations are worth making. First, the user identified by username remoteuser and password secret must reside within the mysql database found on Server A. Second, because this information will be transmitted over a possibly unsecured network to Server A, it’s possible for a third party to capture not only the authentication variables but also the table data. See Chapter 26 for instructions regarding how to mitigate the possibility that a third party could acquire this data and, on the off chance that it happens, how to limit the potential repercussions.
Once created, you can access the Server A products table by accessing the products table on Server B. Furthermore, provided the user assigned in the connection string possesses the necessary privileges, it’s also possible to add, modify, and delete data residing in this remote table .
ARCHIVE
Even given the present availability of low-cost, high-volume storage, organizations such as banks, hospitals, and retailers must take special care to store often enormous amounts of data in the most efficient way possible. Because this data typically must be maintained for long periods of time, even though it’s perhaps rarely accessed, it makes sense to compress it, uncompressing it only when necessary. Catering to such purposes, the ARCHIVE storage engine was added in version 4.1.3.
The ARCHIVE storage engine greatly compresses any data found in a table of this type by using the zlib compression library ( https://www.zlib.net ) and uncompresses it on the fly as records are requested. In addition to selecting records, it’s also possible to insert records, as might be necessary when it becomes practical to migrate aging data over to an ARCHIVE table. However, it’s not possible to delete or update any data stored within these tables.
Note that any data stored in an ARCHIVE table will not be indexed, meaning SELECT operations can be rather inefficient. If for some reason you need to perform extended analysis on an ARCHIVE table, it might make sense to convert the table to MyISAM and re-create the necessary indexes. See the “Storage Engine FAQ” later in this chapter for information about how to convert between engines.
CSV
The CSV storage engine stores table data in a comma-separated format similar to that supported by many applications, such as OpenOffice and Microsoft Office.
Although you access and manipulate CSV tables like any another table type, MyISAM for example, CSV tables are actually text files. This has an interesting implication in that you can actually copy an existing CSV file over the corresponding data file (labeled with a .csv extension) found in MySQL’s designated data folder. Also, given CSV files’ particular format, it’s not possible to take advantage of typical database features such as indexes.
EXAMPLE
Because MySQL’s source code is freely available, you’re free to modify it, provided that you abide by the terms of its respective licenses. Realizing that developers might wish to create new storage engines, MySQL offers the EXAMPLE storage engine as a basic template for understanding how these engines are created.
BLACKHOLE
Available as of MySQL 4.1.11, the BLACKHOLE storage engine operates just like the MyISAM engine except that it won’t store any data. You might use this engine to gauge the overhead incurred by logging because it’s still possible to log the queries even though data will not be stored.
Tip
The BLACKHOLE storage engine isn’t enabled by default, so you need to include the option --with-blackhole-storage-engine at configuration time to use it.
Storage Engine FAQ
There is often a bit of confusion surrounding various issues pertinent to storage engines. Thus, this section is devoted to addressing frequently asked questions about storage engines.
Which Storage Engines Are Available on My Server?
Because several engines aren’t enabled by default, if your desired engine isn’t found in the list, you may need to reconfigure MySQL with a flag that enables the engine.
This list does not show the last three columns of the output. Note that InnoDB is the default engine on Linux. The version of InnoDB is provided by a company called Percona. They have made enhancements to the original version of InnoDB .
How Do I Take Advantage of the Storage Engines on Windows?
By default, the ARCHIVE, BLACKHOLE, CSV, EXAMPLE, FEDERATED, InnoDB, MEMORY, MERGE, and MyISAM storage engines are available on Windows when running MySQL 5.0 or newer. Note that InnoDB is the default when MySQL has been installed using the MySQL Configuration Wizard (see Chapter 23). To use the other supported types, you need to either install the Max version or build MySQL from source.
Is It Wrong to Use Multiple Storage Engines Within the Same Database?
Not at all. In fact, unless you’re working with a particularly simple database, it’s quite likely that your application would benefit from using multiple storage engines. It’s always a good idea to carefully consider the purpose and behavior of each table in your database and choose an appropriate storage engine accordingly. Don’t take the lazy way out and just go with the default storage engine; it could detrimentally affect your application’s performance in the long term.
How Can I Specify a Storage Engine at Creation Time or Change It Later?
You can selectively assign storage engines at creation time by passing along the attribute TYPE=TABLE_TYPE . You can convert a table later with the ALTER command or by using the mysql_convert_table_format script that comes with your MySQL distribution, or use one of the manu GUI clients that provides an easy way to do this.
I Need Speed! What’s the Fastest Storage Engine?
Because MEMORY tables are stored in memory, they offer an extremely fast response time. However, keep in mind that anything stored in memory is highly volatile and is going to disappear if the server or MySQL crashes or is shut down. Although MEMORY tables certainly serve an important purpose, you might want to consider other optimization routes if speed is your goal. You can start by taking time to properly design your tables, always choosing the best possible data type and storage engine. Also, be diligent in optimizing your queries and MySQL server configuration, and of course never skimp on the server hardware. In addition, you can take advantage of MySQL features such as query caching.
Data Types and Attributes
Wielding a strict level of control over the data placed into each column of your MySQL tables is crucial to the success of your data-driven applications. For example, you might want to make sure that the value doesn’t surpass a maximum limit, fall out of the bounds of a specific format, or even constrain the allowable values to a predefined set. To help in this task, MySQL offers an array of data types that can be assigned to each column in a table. Each force the data to conform to a predetermined set of rules inherent to that data type, such as size, type (string, integer, or decimal, for instance), and format (ensuring that it conforms to a valid date or time representation, for example).
The behavior of these data types can be further tuned through the inclusion of attributes. This section introduces both MySQL’s supported data types and many of the commonly used attributes. Because many data types support the same attributes, the attribute definitions won’t be repeated in each data type section; instead, the attribute definitions are grouped under the heading “Data Type Attributes,” following the “Data Types” section.
Data Types
This section introduces MySQL’s supported data types, offering information about the name, purpose, format, and range of each. To facilitate later reference, they’re broken down into three categories: date and time, numeric, and string.
Date and Time Data Types
Many types are available for representing time- and date-based data.
DATE
The DATE data type is responsible for storing date information. Although MySQL displays DATE values in a standard YYYY-MM-DD format, the values can be inserted using either numbers or strings. For example, both 20100810 and 2010-08-10 would be accepted as valid input. The range is 1000-01-01 to 9999-12-31.
Note
For all date and time data types, MySQL will accept any type of nonalphanumeric delimiter to separate the various date and time values. For example, 20080810, 2008*08*10, 2010, 08, 10, and 2010!08!10 are all the same as far as MySQL is concerned.
DATETIME
The DATETIME data type is responsible for storing a combination of date and time information. Like DATE, DATETIME values are stored in a standard format, YYYY-MM-DD HH:MM:SS; the values can be inserted using either numbers or strings. For example, both 20100810153510 and 2010-08-10 15:35:10 would be accepted as valid input. The range of DATETIME is 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
TIME
The TIME data type is responsible for storing time information and supports a range large enough not only to represent both standard- and military-style time formats, but also to represent extended time intervals. This range is –838:59:59 to 838:59:59.
TIMESTAMP [DEFAULT] [ON UPDATE]
The TIMESTAMP data type differs from DATETIME in that MySQL’s default behavior is to automatically update it to the current date and time whenever an INSERT or UPDATE operation affecting it is executed. TIMESTAMP values are displayed in HH:MM:SS format, and, like the DATE and DATETIME data types, you can assign values using either numbers or strings. The range of TIMESTAMP is 1970-01-01 00:00:01 to 2037-12-31 23:59:59. Its storage requirement is 4 bytes.
Caution
When an invalid value is inserted into a DATE , DATETIME , TIME , or TIMESTAMP column, it appears as a string of zeros formatted according to the specifications of the data type.
TIMESTAMP DEFAULT 20080831120000: Starting with version 4.1.2, the first TIMESTAMP defined in a table will accept a default value.
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP: The first TIMESTAMP column defined in a table assumes the value of the current timestamp, and is again updated to the current timestamp each time the row is updated.
TIMESTAMP: When the first TIMESTAMP column is defined in a table as such, it’s the same as defining it with both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.
TIMESTAMP DEFAULT CURRENT_TIMESTAMP: The first TIMESTAMP column defined in a table assumes the value of the current timestamp, but it will not update to the current timestamp each time the row is updated.
TIMESTAMP ON UPDATE CURRENT_TIMESTAMP: The first TIMESTAMP column defined in a table is assigned 0 when the row is inserted, and it is updated to the current timestamp when the row is updated.
YEAR[(2|4)]
Two-digit number: 1 to 99. Values ranging between 1 and 69 are converted to values in the range 2001 to 2069, while values ranging between 70 and 99 are converted to values in the range 1970 to 1999.
Four-digit number: 1901 to 2155.
Two-digit string: "00" to "99." Values ranging between "00" and "69" are converted to values in the range "2000" to "2069," while values ranging between "70" and "99" are converted to values in the range "1970" to "1999."
Four-digit string: "1901" to "2155."
Numeric Data Types
Numerous types are available for representing numerical data.
Note
Many of the numeric data types allow you to constrain the maximum display size, denoted by the M parameter following the type name in the following definitions. Many of the floating-point types allow you to specify the number of digits that should follow the decimal point, denoted by the D parameter . These parameters, along with related attributes, are optional and are indicated as such by their enclosure in square brackets.
BOOL, BOOLEAN
BOOL and BOOLEAN are just aliases for TINYINT(1), intended for assignments of either 0 or 1. This data type was added in version 4.1.0.
BIGINT [(M)]
The BIGINT data type offers MySQL’s largest integer range, supporting a signed range of –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 and an unsigned range of 0 to 18,446,744,073,709,551,615.
INT [(M)] [UNSIGNED] [ZEROFILL]
The INT data type offers MySQL’s second-largest integer range, supporting a signed range of –2,147,483,648 to 2,147,483,647 and an unsigned range of 0 to 4,294,967,295.
MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL]
The MEDIUMINT data type offers MySQL’s third-largest integer range, supporting a signed range of –8,388,608 to 8,388,607 and an unsigned range of 0 to 16,777,215.
SMALLINT [(M)] [UNSIGNED] [ZEROFILL]
The SMALLINT data type offers MySQL’s fourth-largest integer range, supporting a signed range of –32,768 to 32,767 and an unsigned range of 0 to 65,535.
TINYINT [(M)] [UNSIGNED] [ZEROFILL]
The TINYINT data type is MySQL’s smallest integer range, supporting a signed range of –128 to 127 and an unsigned range of 0 to 255.
DECIMAL([M[,D]]) [UNSIGNED] [ZEROFILL]
The DECIMAL data type is a floating-point number stored as a string, supporting a signed range of –1.7976931348623157E+308 to –2.2250738585072014E–308 and an unsigned range of 2.2250738585072014E–308 to 1.7976931348623157E+308. The decimal point and minus sign are ignored when determining the number’s total size.
DOUBLE([M,D]) [UNSIGNED] [ZEROFILL]
The DOUBLE data type is a double-precision floating-point number, supporting a signed range of –1.7976931348623157E+308 to –2.2250738585072014E–308 and an unsigned range of 2.2250738585072014E–308 to 1.7976931348623157E+308.
FLOAT([M,D]) [UNSIGNED] [ZEROFILL]
This FLOAT data type variation is MySQL’s single-precision floating-point number representation, supporting a signed range of –3.402823466E+38 to –1.175494351E–38 and an unsigned range of 1.175494351E–38 to 3.402823466E+38.
FLOAT (precision) [UNSIGNED] [ZEROFILL]
This FLOAT data type variant is provided for ODBC compatibility. The degree of precision can range between 1 to 24 for single precision and 25 to 53 for double precision. The range is the same as that defined in the preceding FLOAT definition.
String Data Types
Many types are available for representing string data.
[NATIONAL] CHAR(Length) [BINARY | ASCII | UNICODE]
The CHAR data type offers MySQL’s fixed-length string representation, supporting a maximum length of 255 characters. If an inserted string does not occupy all of the Length spaces, the remaining space will be padded by blank spaces. When retrieved, these blank spaces are omitted. If Length is one character, the user can omit the length reference, simply using CHAR. You can also specify a zero-length CHAR in conjunction with the NOT NULL attribute, which will allow only NULL or "". The NATIONAL attribute is available for compatibility reasons because that is how SQL-99 specifies that the default character set should be used for the column, which MySQL already does by default. Supplying the BINARY attribute causes the values in this column to be sorted in case-sensitive fashion; omitting it causes them to be sorted in case-insensitive fashion.
If Length is greater than 255, the column will automatically be converted to the smallest TEXT type capable of storing values designated by the provided length. Also starting with version 4.1.0, including the ASCII attribute will result in the application of the Latin1 character set to the column. Finally, beginning with version 4.1.1, including the UNICODE attribute will result in the application of the ucs2 character set to the column.
[NATIONAL] VARCHAR(Length) [BINARY]
The VARCHAR data type is MySQL’s variable-length string representation, supporting a length of 0 to 65,535 characters as of version 5.0.3; 0 to 255 characters as of version 4.0.2; and 1 to 255 characters prior to version 4.0.2. The NATIONAL attribute is available for compatibility reasons, because that is how SQL-99 specifies that the default character set should be used for the column (which MySQL already does by default). Supplying the BINARY attribute causes the values in this column to be sorted in case-sensitive fashion; omitting it causes them to be sorted in case-insensitive fashion.
Historically, any trailing spaces were not stored by VARCHAR; however, as of version 5.0.3, they are stored for reasons of standards compliance.
LONGBLOB
The LONGBLOB data type is MySQL’s largest binary string representation, supporting a maximum length of 4,294,967,295 characters.
LONGTEXT
The LONGTEXT data type is MySQL’s largest nonbinary string representation, supporting a maximum length of 4,294,967,295 characters.
MEDIUMBLOB
The MEDIUMBLOB data type is MySQL’s second-largest binary string representation, supporting a maximum of 16,777,215 characters.
MEDIUMTEXT
The MEDIUMTEXT data type is MySQL’s second-largest nonbinary text string, capable of storing a maximum length of 16,777,215 characters.
BLOB
The BLOB data type is MySQL’s third-largest binary string representation, supporting a maximum length of 65,535 characters.
TEXT
The TEXT data type is MySQL’s third-largest nonbinary string representation, supporting a maximum length of 65,535 characters.
TINYBLOB
The TINYBLOB data type is MySQL’s smallest binary string representation, supporting a maximum length of 255 characters.
TINYTEXT
The TINYTEXT data type is MySQL’s smallest nonbinary string representation, supporting a maximum length of 255 characters.
ENUM(“member1”,“member2”,…“member65,535”)
The ENUM data type provides a means for storing a maximum of one member chosen from a predefined group consisting of a maximum of 65,535 distinct members. The choice of members is restricted to those declared in the column definition. If the column declaration includes the NULL attribute, then NULL will be considered a valid value and will be the default. If NOT NULL is declared, the first member of the list will be the default.
SET(“member1”, “member2”,…“member64”)
The SET data type provides a means for specifying zero or more values chosen from a predefined group consisting of a maximum of 64 members. The choice of values is restricted to those declared in the column definition. The storage requirement is 1, 2, 3, 4, or 8 values, depending on the number of members. You can determine the exact requirement with this formula: (N+7)/8, where N is the set size.
Spatial Data Types
Spatial data types are complex data types with multiple scalar values. An example is a point that is defined by two values or a polygon that has multiple values that describe the x and y coordinates of each point in the polygon. The supported spatial data types are GEOMETRY, POINT, LINESTRING, and POLYGON. These types can store a single value of each type. A set of spatial data types that can store a collection of values is also available. These are called MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION. See https://dev.mysql.com/doc/refman/5.7/en/spatial-types.html for more information.
JSON Data Types
JSON is a textual representation of JavaScript objects, and it can be stored in a string column, but using a string column has some limitations when it comes to searching. The native JSON column type performs validation when the data is inserted or updated. It is possible to select parts of the JSON object or select rows where the JSON object has specific values. More information can be found here https://dev.mysql.com/doc/refman/5.7/en/json.html .
The JSON data type allows you to use the same object format in the database, in your PHP scripts, and in the JavaScript front-end application.
Data Type Attributes
Although this list is not exhaustive, this section introduces the attributes you’ll most commonly use, as well as those that will be used throughout the remainder of this book.
AUTO_INCREMENT
The AUTO_INCREMENT attribute takes away a level of logic that would otherwise be necessary in many database-driven applications: the ability to assign unique integer identifiers to newly inserted rows. Assigning this attribute to a column will result in the assignment of the last insertion ID +1 to each newly inserted row.
BINARY
DEFAULT
The DEFAULT attribute ensures that some constant value will be assigned when no other value is available. This value must be a constant, because MySQL does not allow functional or expressional values to be inserted. Furthermore, this attribute cannot be used in conjunction with BLOB or TEXT fields. If the NULL attribute has been assigned to this field, the default value will be null if no default is specified. Otherwise (specifically, if NOT NULL is an accompanying attribute), the default value will depend on the field data type.
INDEX
This section offers a slight variation on the previous one, this time indexing only the first seven characters of the first name because more letters probably won’t be necessary to differentiate among first names. Select performance is usually better when smaller indexes are used, so you should strive for smaller indexes whenever practical. Insert performance can be impacted by indices as the server will have to insert the data and create all index entries for the new row. In the case of bulk inserts, it’s often better to drop the indices, insert the data, and then re-create the indices on the table.
NATIONAL
The NATIONAL attribute is used only in conjunction with the CHAR and VARCHAR data types. When specified, it ensures that the column uses the default character set, which MySQL already does by default. In short, this attribute is offered as an aid in database compatibility.
NOT NULL
NULL
The NULL attribute indicates that a column is allowed to have no value. Keep in mind that NULL is a mathematical term specifying “nothingness” rather than an empty string or zero. When a column is assigned the NULL attribute , it is possible for the field to remain empty regardless of whether the other row fields have been populated.
The NULL attribute is assigned to a field by default. Typically, you will want to avoid this default, ensuring that empty values will not be accepted into the table. This is accomplished through NULL’s antithesis, NOT NULL , introduced above.
PRIMARY KEY
Single-field primary keys: Single-field primary keys are typically used when there is a preexisting, nonmodifiable unique identifier for each row entered into the database, such as a part number or Social Security number. Note that this key should never change once set. Primary keys should not contain any information other than identifying a specific row in the table.
Multiple-field primary keys: Multiple-field primary keys can be useful when it is not possible to guarantee uniqueness from any single field within a record. Thus, multiple fields are conjoined to ensure uniqueness. An example could be country and zip code. The same zip code could exist in multiple countries, and thus it is necessary to use the combination of country and zip code as the primary key. When such a situation arises, it is often a good idea to simply designate an AUTO_INCREMENT integer as the primary key; this alleviates the need to somehow generate unique identifiers with every insertion.
The following three examples demonstrate creation of the auto-increment, single-field, and multiple-field primary key fields, respectively.
UNIQUE
ZEROFILL
Given this definition, the value 35,678 would be returned as 0035678.
Working with Databases and Tables
Learning how to manage and navigate MySQL databases and tables is one of the first tasks you’ll want to master. This section highlights several key tasks.
Working with Databases
This section demonstrates how to view, create, select, and delete MySQL databases.
Viewing Databases
Keep in mind that your ability to view all the available databases on a given server is affected by user privileges. See Chapter 26 for more information about this matter.
Note that using the SHOW DATABASES command is the standard methodology prior to MySQL version 5.0.0. Although the command is still available for versions 5.0.0 and greater, consider using the commands provided to you by way of the INFORMATION_SCHEMA . See the later section titled “The INFORMATION_SCHEMA” for more information about this new feature.
Creating a Database
Common problems for failed database creation include insufficient or incorrect permissions, or an attempt to create a database that already exists.
Using a Database
Deleting a Database
Working with Tables
In this section, you’ll learn how to create, list, review, delete, and alter MySQL database tables.
Creating a Table
Keep in mind that a table must consist of at least one column. Also, you can always go back and alter a table structure after it has been created. Later in this section, you’ll learn how this is accomplished via the ALTER TABLE statement .
Conditionally Creating a Table
One oddity of this action is that the output does not specify whether the table was created. Both variations display the “Query OK” message before returning to the command prompt.
Copying a Table
An identical table, employees2, will be added to the database.
Creating a Temporary Table
Temporary tables are created just as any other table would be, except that they’re stored in the operating system’s designated temporary directory, typically /tmp or /usr/tmp on Linux. You can override this default by setting MySQL’s TMPDIR environment variable.
Note
As of MySQL 4.0.2, ownership of the CREATE TEMPORARY TABLE privilege is required in order to create temporary tables. See Chapter 26 for more details about MySQL’s privilege system.
Viewing a Database’s Available Tables
Note that this is the standard methodology prior to MySQL version 5.0.0. Although the command is still available for versions 5.0.0 and greater, consider using the commands provided to you by way of the INFORMATION_SCHEMA . See the later section titled “The INFORMATION_SCHEMA” for more information about this new feature.
Viewing a Table Structure
You can view a table structure using the DESCRIBE statement :
If you’d like to wield more control over how to parse the schema, consider using the commands provided to you by way of the INFORMATION_SCHEMA, described in the upcoming section “The INFORMATION_SCHEMA.”
Deleting a Table
Altering a Table Structure
The INFORMATION_SCHEMA
Earlier in this chapter, you learned that the SHOW command is used to learn more about the databases found in the server, tables found in a database, and columns comprising a table. In fact, SHOW is used for learning quite a bit about the server’s configuration, including user privileges, supported table engines, executing processes, and more. The problem is that SHOW isn’t a standard database feature; it’s something entirely native to MySQL. Furthermore, it isn’t particularly powerful. For instance, it’s not possible to use the command to learn about a table’s engine type. Nor could one, say, easily find out which columns in a set of given tables are of type VARCHAR. The introduction of the INFORMATION_SCHEMA in version 5.0.2 solves such problems.
CHARACTER_SETS: Stores information about the available character sets.
COLLATIONS: Stores information about character set collations.
COLLATION_CHARACTER_SET_APPLICABILITY: A subset of the INFORMATION_SCHEMA.COLLATIONS table, it matches character sets to each respective collation.
COLUMNS: Stores information about table columns, such as a column’s name, data type, and whether it’s nullable.
COLUMN_PRIVILEGES: Stores information about column privileges. Keep in mind that this information is actually retrieved from the mysql.columns_priv table ; however, retrieving it from this table offers the opportunity for additional uniformity when querying database properties. See Chapter 29 for more information.
ENGINES: Stores information about available storage engines.
EVENTS: Stores information about scheduled events. Scheduled events are out of the scope of this book; consult the MySQL documentation for more information.
FILES: Stores information about NDB disk data tables. NDB is a storage engine that is out of the scope of this book; consult the MySQL documentation for more information.
GLOBAL_STATUS: Stores information about server status variables.
GLOBAL_VARIABLES: Stores information about server settings.
KEY_COLUMN_USAGE: Stores information about key column constraints.
PARTITIONS: Stores information about table partitions.
PLUGINS: Stores information about plug-ins, a feature new to MySQL 5.1 and out of the scope of this book. Consult the MySQL documentation for more information.
PROCESSLIST: Stores information about currently running threads.
PROFILING: Stores information about query profiles. You can also find this information by executing the SHOW PROFILE and SHOW PROFILES commands .
REFERENTIAL_CONSTRAINTS: Stores information about foreign keys.
ROUTINES: Stores information about stored procedures and functions. See Chapter 32 for more about this topic.
SCHEMATA: Stores information about the databases located on the server, such as the database name and default character set.
SCHEMA_PRIVILEGES: Stores information about database privileges. Keep in mind that this information is actually retrieved from the mysql.db table; however, retrieving it from this table offers the opportunity for additional uniformity when querying database properties. See Chapter 29 for more information about this topic.
SESSION_STATUS: Stores information about the current session.
SESSION_VARIABLES: Stores information about the current session’s configuration.
STATISTICS: Stores information about each table index, such as the column name, whether it’s nullable, and whether each row must be unique.
TABLES: Stores information about each table, such as the name, engine, creation time, and average row length.
TABLE_CONSTRAINTS: Stores information about table constraints, such as whether it includes UNIQUE and PRIMARY KEY columns .
TABLE_PRIVILEGES: Stores information about table privileges. Keep in mind that this information is actually retrieved from the mysql.tables_priv table ; however, retrieving it from this table offers the opportunity for additional uniformity when querying database properties. See Chapter 29 for more information.
TRIGGERS: Stores information about each trigger, such as whether it fires according to an insertion, deletion, or modification. Note that this table wasn’t added to the INFORMATION_SCHEMA until version 5.0.10. See Chapter 33 for more information.
USER_PRIVILEGES: Stores information about global privileges. Keep in mind that this information is actually retrieved from the mysql.user table ; however, retrieving it from this table offers the opportunity for additional uniformity when querying database properties. See Chapter 29 for more information.
VIEWS: Stores information about each view, such as its definition and whether it’s updatable. See Chapter 34 for more information.
As you can see even from these brief examples, using SELECT queries to retrieve this information is infinitely more flexible than using SHOW . Also, it’s unlikely the SHOW command will disappear anytime soon. Therefore, if you’re just looking for a quick summary of, say, databases found on the server, you’ll certainly save a few keystrokes by continuing to use SHOW .
Summary
In this chapter, you learned about the many ingredients that go into MySQL table design. The chapter kicked off the discussion with a survey of MySQL’s storage engines, discussing the purpose and advantages of each. This discussion was followed by an introduction to MySQL’s supported data types, offering information about the name, purpose, and range of each. Then you examined many of the most commonly used attributes, which serve to further tweak column behavior. The chapter then moved on to a short tutorial on basic MySQL administration commands, demonstrating how databases and tables are listed, created, deleted, perused, and altered. Finally, you were introduced to the INFORMATION_SCHEMA feature found in MySQL 5.0.2 and newer. This chapter also touched on the database called MariaDB and the fact that the database mostly is compatible with MySQL as it shares the same roots.
The next chapter dives into another key MySQL feature: security. You’ll learn all about MySQL’s powerful privilege tables. You’ll also learn how to secure the MySQL server daemon and create secure MySQL connections using SSL.