Here are examples of how to solve some common problems with MySQL.
Some of the examples use the table shop
to hold
the price of each article (item number) for certain traders
(dealers). Supposing that each trader has a single fixed price per
article, then (article
,
dealer
) is a primary key for the records.
Start the command-line tool mysql and select a database:
shell>mysql
your-database-name
(In most MySQL installations, you can use the database named
test
).
You can create and populate the example table with these statements:
mysql>CREATE TABLE shop (
->article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
->dealer CHAR(20) DEFAULT '' NOT NULL,
->price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
->PRIMARY KEY(article, dealer));
mysql>INSERT INTO shop VALUES
->(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
->(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
After issuing the statements, the table should have the following contents:
mysql> SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
“What's the highest item number?”
SELECT MAX(article) AS article FROM shop; +---------+ | article | +---------+ | 4 | +---------+
Task: Find the number, dealer, and price of the most expensive article.
This is easily done with a subquery:
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);
Another solution is to sort all rows descending by price and get
only the first row using the MySQL-specific
LIMIT
clause:
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
Note: If there were several
most expensive articles, each with a price of 19.95, the
LIMIT
solution would show only one of them.
Task: Find the highest price per article.
SELECT article, MAX(price) AS price FROM shop GROUP BY article +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+
Task: For each article, find the dealer or dealers with the most expensive price.
This problem can be solved with a subquery like this one:
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
You can employ MySQL user variables to remember results without having to store them in temporary variables in the client. (See Section 9.3, “User-Defined Variables”.)
For example, to find the articles with the highest and lowest price you can do this:
mysql>SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql>SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+
In MySQL, InnoDB
tables support checking of
foreign key constraints. See Section 14.2, “The InnoDB
Storage Engine”, and
Section 1.9.5.5, “Foreign Keys”.
A foreign key constraint is not required merely to join two
tables. For storage engines other than
InnoDB
, it is possible when defining a column
to use a REFERENCES
tbl_name
(col_name
)
clause, which has no actual effect, and serves only as
a memo or comment to you that the column which you are currently
defining is intended to refer to a column in another
table. It is extremely important to realize when
using this syntax that:
-
MySQL does not perform any sort of
CHECK
to make sure thatcol_name
actually exists intbl_name
(or even thattbl_name
itself exists). -
MySQL does not perform any sort of action on
tbl_name
such as deleting rows in response to actions taken on rows in the table which you are defining; in other words, this syntax induces noON DELETE
orON UPDATE
behavior whatsoever. (Although you can write anON DELETE
orON UPDATE
clause as part of theREFERENCES
clause, it is also ignored.) -
This syntax creates a column; it does not create any sort of index or key.
-
This syntax will cause an error if used in trying to define an
InnoDB
table.
You can use a column so created as a join column, as shown here:
CREATE TABLE person ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(60) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE shirt ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, style ENUM('t-shirt', 'polo', 'dress') NOT NULL, color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL, owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id), PRIMARY KEY (id) ); INSERT INTO person VALUES (NULL, 'Antonio Paz'); SELECT @last := LAST_INSERT_ID(); INSERT INTO shirt VALUES (NULL, 'polo', 'blue', @last), (NULL, 'dress', 'white', @last), (NULL, 't-shirt', 'blue', @last); INSERT INTO person VALUES (NULL, 'Lilliana Angelovska'); SELECT @last := LAST_INSERT_ID(); INSERT INTO shirt VALUES (NULL, 'dress', 'orange', @last), (NULL, 'polo', 'red', @last), (NULL, 'dress', 'blue', @last), (NULL, 't-shirt', 'white', @last); SELECT * FROM person; +----+---------------------+ | id | name | +----+---------------------+ | 1 | Antonio Paz | | 2 | Lilliana Angelovska | +----+---------------------+ SELECT * FROM shirt; +----+---------+--------+-------+ | id | style | color | owner | +----+---------+--------+-------+ | 1 | polo | blue | 1 | | 2 | dress | white | 1 | | 3 | t-shirt | blue | 1 | | 4 | dress | orange | 2 | | 5 | polo | red | 2 | | 6 | dress | blue | 2 | | 7 | t-shirt | white | 2 | +----+---------+--------+-------+ SELECT s.* FROM person p INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' AND s.color <> 'white'; +----+-------+--------+-------+ | id | style | color | owner | +----+-------+--------+-------+ | 4 | dress | orange | 2 | | 5 | polo | red | 2 | | 6 | dress | blue | 2 | +----+-------+--------+-------+
When used in this fashion, the REFERENCES
clause is not displayed in the output of SHOW CREATE
TABLE
or DESCRIBE
:
SHOW CREATE TABLE shirt\G *************************** 1. row *************************** Table: shirt Create Table: CREATE TABLE `shirt` ( `id` smallint(5) unsigned NOT NULL auto_increment, `style` enum('t-shirt','polo','dress') NOT NULL, `color` enum('red','blue','orange','white','black') NOT NULL, `owner` smallint(5) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
The use of REFERENCES
in this way as a
comment or “reminder” in a column definition works
with both MyISAM
and
BerkeleyDB
tables.
An OR
using a single key is well optimized,
as is the handling of AND
.
The one tricky case is that of searching on two different keys
combined with OR
:
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'
This case is optimized from MySQL 5.0.0. See Section 7.2.6, “Index Merge Optimization”.
You can also solve the problem efficiently by using a
UNION
that combines the output of two
separate SELECT
statements. See
Section 13.2.7.2, “UNION
Syntax”.
Each SELECT
searches only one key and can be
optimized:
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' UNION SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
The following example shows how you can use the bit group functions to calculate the number of days per month a user has visited a Web page.
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL); INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23);
The example table contains year-month-day values representing visits by users to the page. To determine how many different days in each month these visits occur, use this query:
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;
Which returns:
+------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 01 | 3 | | 2000 | 02 | 2 | +------+-------+------+
The query calculates how many different days appear in the table for each year/month combination, with automatic removal of duplicate entries.
The AUTO_INCREMENT
attribute can be used to
generate a unique identity for new rows:
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich'); SELECT * FROM animals;
Which returns:
+----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+
You can retrieve the most recent
AUTO_INCREMENT
value with the
LAST_INSERT_ID()
SQL function or the
mysql_insert_id()
C API function. These
functions are connection-specific, so their return values are
not affected by another connection which is also performing
inserts.
Note: For a multiple-row
insert, LAST_INSERT_ID()
and
mysql_insert_id()
actually return the
AUTO_INCREMENT
key from the
first of the inserted rows. This allows
multiple-row inserts to be reproduced correctly on other servers
in a replication setup.
For MyISAM
and BDB
tables
you can specify AUTO_INCREMENT
on a secondary
column in a multiple-column index. In this case, the generated
value for the AUTO_INCREMENT
column is
calculated as
MAX(
auto_increment_column
) +
1 WHERE
prefix=given-prefix
. This
is useful when you want to put data into ordered groups.
CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id) ); INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich'); SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 1 | dog | | mammal | 2 | cat | | mammal | 3 | whale | | bird | 1 | penguin | | bird | 2 | ostrich | +--------+----+---------+
Note that in this case (when the
AUTO_INCREMENT
column is part of a
multiple-column index), AUTO_INCREMENT
values
are reused if you delete the row with the biggest
AUTO_INCREMENT
value in any group. This
happens even for MyISAM
tables, for which
AUTO_INCREMENT
values normally are not
reused.
If the AUTO_INCREMENT
column is part of
multiple indexes, MySQL will generate sequence values using the
index that begins with the AUTO_INCREMENT
column, if there is one. For example, if the
animals
table contained indexes
PRIMARY KEY (grp, id)
and INDEX
(id)
, MySQL would ignore the PRIMARY
KEY
for generating sequence values. As a result, the
table would contain a single sequence, not a sequence per
grp
value.
To start with an AUTO_INCREMENT
value other
than 1, you can set that value with CREATE
TABLE
or ALTER TABLE
, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
More information about AUTO_INCREMENT
is
available here:
-
How to assign the
AUTO_INCREMENT
attribute to a column: Section 13.1.5, “CREATE TABLE
Syntax”, and Section 13.1.2, “ALTER TABLE
Syntax”. -
How
AUTO_INCREMENT
behaves depending on the SQL mode: Section 5.2.5, “The Server SQL Mode”. -
Find the row that contains the most recent AUTO_INCREMENT value: Section 12.1.3, “Comparison Functions and Operators”.
-
Set the
AUTO_INCREMENT
value to be used: Section 13.5.3, “SET
Syntax”. -
AUTO_INCREMENT
and replication: Section 6.7, “Replication Features and Known Problems”. -
Server-system variables related to
AUTO_INCREMENT
(auto_increment_increment
andauto_increment_offset
) that can be used for replication: Section 5.2.2, “Server System Variables”.