Internet

Working with Tables

Creating Tables the Other way

One of the key concepts of relational databases is that everything is represented as a table of rows and columns, and the result of every SELECT is also a table of rows and columns.

In many cases, the "table" that results from a SELECT is just an image of rows and columns that scroll off the top of your display as you continue working. But sometimes it is desirable to save a query result in another table so that you can refer to it later.

As of MySQL 3.23.0, you can do that easily. Use a CREATE TABLE ... SELECT statement to cause a new table to spring into existence on-the-fly to hold the result of an arbitrary SELECT query. You can do this in a single step without having to know or specify the data types of the columns you're retrieving.

This makes it exceptionally easy to create a table fully populated with the data you're interested in, ready to be used in further queries. For example, the following statement creates a new table named student_f that consists of information for all female students in the student table:

CREATE TABLE student_f SELECT * FROM student WHERE sex = 'f';

To copy an entire table, omit the WHERE clause:

CREATE TABLE new_tbl_name SELECT * FROM tbl_name;

Or, to create an empty copy, use a WHERE clause that always evaluates to false:

CREATE TABLE new_tbl_name SELECT * FROM tbl_name WHERE 0;

Creating an empty copy of a table is useful if you want to load a data file into the original table using LOAD DATA, but you're not sure if you have the options for specifying the data format quite right.

You don't want to end up with malformed records in the original table if you don't get the options right the first time! Using an empty copy of the original table allows you to experiment with the LOAD DATA options for specifying column and line delimiters until you're satisfied your input records are being interpreted properly.

After you're satisfied, you can load the file into the original table. Do that either by rerunning the LOAD DATA statement with the original table name or by copying the data into it from the copy:

INSERT INTO orig_tbl SELECT * FROM copy_tbl;

You can combine CREATE TEMPORARY TABLE with SELECT to retrieve a table's contents into a temporary copy of itself:

CREATE TEMPORARY TABLE mytbl SELECT * FROM mytbl;

That allows you to modify the contents of mytbl without affecting the original, which can be useful when you want to try out some queries that modify the contents of the table, but you don't want to change the original table.

To use pre-written scripts that use the original table name, you don't need to edit them to refer to a different table; just add the CREATE TEMPORARY TABLE statement to the beginning of the script. The script will create a temporary copy and operate on the copy, and the server will delete the copy when the script finishes.

Note: One caution to observe here is that some clients, such as mysql, attempt to reconnect to the server automatically if the connection drops. Should this happen when you're working with the temporary table, it will be dropped and the queries executed subsequent to reconnecting will use the original table. Keep this in mind if you have an unreliable network.

To create a table as an empty copy of itself, use a WHERE clause that is never true in conjunction with CREATE TEMPORARY TABLE ... SELECT:

CREATE TEMPORARY TABLE mytbl SELECT * FROM mytbl WHERE 0;

Creating a table on-the-fly from the results of a SELECT statement is a powerful capability, but there are several issues to consider when doing this.

With CREATE TABLE ... SELECT, you should use aliases as necessary to provide reasonable column names. When you create a table by selecting data into it, the column names are taken from the columns that you are selecting.

If a column is calculated as the result of an expression, the "name" of the column is the text of the expression. Prior to MySQL 3.23.6, the following statement will fail outright, because expressions aren't legal as column names:

mysql> create table my_test_tbl6 select PI();
ERROR 1166: Incorrect column name 'PI()'

From 3.23.6 on, column naming rules are relaxed, so the statement will succeed but create a table with an unusual column name:

mysql> create table my_test_tbl6 select PI();
mysql> SELECT * FROM mytbl;

+-----------+
| PI()      |
+-----------+
| 3.141593 |
+-----------+

That's unfortunate, because the column name can be referred to directly only by enclosing it within backticks:

mysql> SELECT `PI()` FROM my_test_tbl6;

+----------+
| PI()     |
+----------+
| 3.141593|
+----------+

To provide a column name that is easier to work with when selecting an expression, use an alias:

mysql> CREATE TABLE my_test_tbl7 SELECT PI() AS mycol;
mysql> SELECT mycol FROM my_test_tbl7;

+----------+
| mycol    |
+----------+
| 3.141593|
+----------+

A related snag occurs if you select columns from different tables that have the same name. Suppose tables t1 and t2 both have a column c and you want to create a table from all combinations of rows in both tables. The following statement will fail because it attempts to create a table with two columns named c:

mysql> CREATE TABLE t3 SELECT * FROM t1, t2;
ERROR 1060: Duplicate column name 'c'

You can provide aliases to specify unique column names in the new table:

mysql> CREATE TABLE t3 SELECT t1.c AS c1, t2.c AS c2 FROM t1, t2;

Another thing to watch out for is that characteristics of the original table that are not reflected in the selected data will not be incorporated into the structure of the new table.

For example, creating a table by selecting data into it does not automatically copy any indexes from the original table, because result sets are not themselves indexed. Similarly, column attributes such as AUTO_INCREMENT or the default value may not be carried into the new table.

. (Newer versions do better than older ones.) In some cases, you can force specific attributes to be used in the new table by invoking the CAST() function, which is available as of MySQL 4.0.2.

The following CREATE TABLE ... SELECT statement forces the columns produced by the SELECT to be treated as INT UNSIGNED, DATE, and CHAR BINARY, which you can verify with DESCRIBE:

mysql> CREATE TABLE mytbl SELECT
-> CAST(1 AS UNSIGNED) AS i,
-> CAST(CURDATE() AS DATE) AS d,
-> CAST('Hello, world' AS BINARY) AS c;
mysql> DESCRIBE mytbl; ;

+-------+-----------------+------+-----+------------+-------+
| Field | Type            | Null | Key | Default    | Extra |
+-------+-----------------+------+-----+------------+-------+
| i     | int(1) unsigned |      |     | 0          |       |
| d     | date            |      |     | 0000-00-00 |       |
| c     | char(12) binary |      |     |            |       |
+-------+-----------------+------+-----+------------+-------+

You can apply CAST() to column values retrieved from other tables as well. The allowable cast types are BINARY (binary string), DATE, DATETIME, TIME, SIGNED, SIGNED INTEGER, UNSIGNED, and UNSIGNED INTEGER.

As of MySQL 4.1, it's possible to provide even more information about the types that you want the columns in the new table to have by giving explicit definitions for them. Columns in the table are matched with the selected columns by name, so provide aliases for the selected columns if necessary to cause them to match up properly:

mysql> CREATE TABLE mytbl (i INT UNSIGNED, d DATE, c CHAR(20) BINARY)
-> SELECT -> 1 AS i,
-> CURDATE() AS d,
-> 'Hello, world' AS c;
mysql> DESCRIBE mytbl;

+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| i     | int(10) unsigned | YES  |     | NULL    |       |
| d     | date             | YES  |     | NULL    |       |
| c     | char(20) binary  | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+

Note that this allows you to create character columns that have a different width than that of the longest value in the result set. Also note that the Null and Default attributes of the columns are different for this example than for the previous one. You could provide explicit declarations for those attributes as well if necessary.

Prior to MySQL 3.23, CREATE TABLE ... SELECT is unavailable. If you want to save the results of a SELECT in a table for use in further queries, you must make special arrangements in advance:

1. Run a DESCRIBE or SHOW COLUMNS query to determine the types of the columns in the tables from which you want to capture information.

2. Issue an explicit CREATE TABLE statement to create the table into which you want to save the SELECT results. The statement should specify the names and types of the columns that the SELECT will retrieve.

3. After creating the table, issue an INSERT INTO ... SELECT query to retrieve the results and insert them into the table.

Clearly, compared to CREATE TABLE ... SELECT, this involves a lot of ugly messing around.