Menu:
example-auto-increment:: Using AUTO_INCREMENT
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 *note 'mysql': mysql. and select a database:
$> mysql YOUR-DATABASE-NAMETo create and populate the example table, use these statements:
CREATE TABLE shop (
article INT UNSIGNED DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
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:
SELECT * FROM shop ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 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 |
+---------+--------+-------+File: manual.info.tmp, Node: example-maximum-column, Next: example-maximum-row, Prev: examples, Up: examples
'What is the highest item number?'
SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+File: manual.info.tmp, Node: example-maximum-row, Next: example-maximum-column-group, Prev: example-maximum-column, Up: examples
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);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+Another solution is to use a 'LEFT JOIN', as shown here:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;You can also do this by sorting all rows descending by price and get only the first row using the MySQL-specific 'LIMIT' clause, like this:
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.
File: manual.info.tmp, Node: example-maximum-column-group, Next: example-maximum-column-group-row, Prev: example-maximum-row, Up: examples
Task: Find the highest price per article.
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
ORDER BY article;
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+File: manual.info.tmp, Node: example-maximum-column-group-row, Next: example-user-variables, Prev: example-maximum-column-group, Up: examples
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)
ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+The preceding example uses a correlated subquery, which can be inefficient (see *note correlated-subqueries::). Other possibilities for solving the problem are to use an uncorrelated subquery in the 'FROM' clause or a 'LEFT JOIN'.
Uncorrelated subquery:
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;'LEFT JOIN':
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;The 'LEFT JOIN' works on the basis that when 's1.price' is at its maximum value, there is no 's2.price' with a greater value and thus the corresponding 's2.article' value is 'NULL'. See *note join::.
File: manual.info.tmp, Node: example-user-variables, Next: example-foreign-keys, Prev: example-maximum-column-group-row, Up: examples
You can employ MySQL user variables to remember results without having to store them in temporary variables in the client. (See *note user-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 |
+---------+--------+-------+Note:
It is also possible to store the name of a database object such as a table or a column in a user variable and then to use this variable in an SQL statement; however, this requires the use of a prepared statement. See *note sql-prepared-statements::, for more information.
File: manual.info.tmp, Node: example-foreign-keys, Next: searching-on-two-keys, Prev: example-user-variables, Up: examples
MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent.
A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.
This following example relates 'parent' and 'child' tables through a single-column foreign key and shows how a foreign key constraint enforces referential integrity.
Create the parent and child tables using the following SQL statements:
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
) ENGINE=INNODB;Insert a row into the parent table, like this:
mysql> INSERT INTO parent (id) VALUES (1);Verify that the data was inserted. You can do this simply by selecting all rows from 'parent', as shown here:
mysql> SELECT * FROM parent;
+----+
| id |
+----+
| 1 |
+----+Insert a row into the child table using the following SQL statement:
mysql> INSERT INTO child (id,parent_id) VALUES (1,1);The insert operation is successful because 'parent_id' 1 is present in the parent table.
Insertion of a row into the child table with a 'parent_id' value that is not present in the parent table is rejected with an error, as you can see here:
mysql> INSERT INTO child (id,parent_id) VALUES(2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
REFERENCES `parent` (`id`))The operation fails because the specified 'parent_id' value does not exist in the parent table.
Trying to delete the previously inserted row from the parent table also fails, as shown here:
mysql> DELETE FROM parent WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
REFERENCES `parent` (`id`))This operation fails because the record in the child table contains the referenced id ('parent_id') value.
When an operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by 'ON UPDATE' and 'ON DELETE' subclauses of the 'FOREIGN KEY' clause. Omitting 'ON DELETE' and 'ON UPDATE' clauses (as in the current child table definition) is the same as specifying the 'RESTRICT' option, which rejects operations that affect a key value in the parent table that has matching rows in the parent table.
To demonstrate 'ON DELETE' and 'ON UPDATE' referential actions, drop the child table and recreate it to include 'ON UPDATE' and 'ON DELETE' subclauses with the 'CASCADE' option. The 'CASCADE' option automatically deletes or updates matching rows in the child table when deleting or updating rows in the parent table.
DROP TABLE child;
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=INNODB;Insert some rows into the child table using the statement shown here:
mysql> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);Verify that the data was inserted, like this:
mysql> SELECT * FROM child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+------+-----------+Update the ID in the parent table, changing it from 1 to 2, using the SQL statement shown here:
mysql> UPDATE parent SET id = 2 WHERE id = 1;Verify that the update was successful by selecting all rows from the parent table, as shown here:
mysql> SELECT * FROM parent;
+----+
| id |
+----+
| 2 |
+----+Verify that the 'ON UPDATE CASCADE' referential action updated the child table, like this:
mysql> SELECT * FROM child;
+------+-----------+
| id | parent_id |
+------+-----------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
+------+-----------+To demonstrate the 'ON DELETE CASCADE' referential action, delete records from the parent table where 'parent_id = 2'; this deletes all records in the parent table.
mysql> DELETE FROM parent WHERE id = 2;Because all records in the child table are associated with 'parent_id = 2', the 'ON DELETE CASCADE' referential action removes all records from the child table, as shown here:
mysql> SELECT * FROM child;
Empty set (0.00 sec)For more information about foreign key constraints, see *note create-table-foreign-keys::.
File: manual.info.tmp, Node: searching-on-two-keys, Next: calculating-days, Prev: example-foreign-keys, Up: examples
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. See *note index-merge-optimization::.
You can also solve the problem efficiently by using a note 'UNION': union. that combines the output of two separate note 'SELECT': select. statements. See *note union::.
Each *note 'SELECT': 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';File: manual.info.tmp, Node: calculating-days, Next: example-auto-increment, Prev: searching-on-two-keys, Up: examples
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, month INT UNSIGNED,
day INT UNSIGNED);
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 | 1 | 3 |
| 2000 | 2 | 2 |
+------+-------+------+The query calculates how many different days appear in the table for each year/month combination, with automatic removal of duplicate entries.
File: manual.info.tmp, Node: example-auto-increment, Prev: calculating-days, Up: examples
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 |
+----+---------+No value was specified for the 'AUTO_INCREMENT' column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the 'NO_AUTO_VALUE_ON_ZERO' SQL mode is enabled. For example:
INSERT INTO animals (id,name) VALUES(0,'groundhog');If the column is declared 'NOT NULL', it is also possible to assign 'NULL' to the column to generate sequence numbers. For example:
INSERT INTO animals (id,name) VALUES(NULL,'squirrel');When you insert any other value into an 'AUTO_INCREMENT' column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value. For example:
INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | groundhog |
| 8 | squirrel |
| 100 | rabbit |
| 101 | mouse |
+-----+-----------+Updating an existing 'AUTO_INCREMENT' column value in an 'InnoDB' table does not reset the 'AUTO_INCREMENT' sequence as it does for 'MyISAM' and 'NDB' tables.
You can retrieve the most recent automatically generated 'AUTO_INCREMENT' value with the 'LAST_INSERT_ID()' SQL function or the 'mysql_insert_id()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-insert-id.html) C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.
Use the smallest integer data type for the 'AUTO_INCREMENT' column that is large enough to hold the maximum sequence value you need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. Use the 'UNSIGNED' attribute if possible to allow a greater range. For example, if you use note 'TINYINT': integer-types, the maximum permissible sequence number is 127. For note 'TINYINT UNSIGNED': integer-types, the maximum is 255. See *note integer-types:: for the ranges of all the integer types.
Note:
For a multiple-row insert, 'LAST_INSERT_ID()' and 'mysql_insert_id()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-insert-id.html) actually return the 'AUTO_INCREMENT' key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.
To start with an 'AUTO_INCREMENT' value other than 1, set that value with note 'CREATE TABLE': create-table. or note 'ALTER TABLE': alter-table, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;InnoDB Notes
For information about 'AUTO_INCREMENT' usage specific to 'InnoDB', see *note innodb-auto-increment-handling::.
MyISAM Notes
For 'MyISAM' 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)
) ENGINE=MyISAM;
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 |
+--------+----+---------+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 generates 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.
Further Reading
More information about 'AUTO_INCREMENT' is available here:
How to assign the 'AUTO_INCREMENT' attribute to a column: note create-table::, and note alter-table::.
How 'AUTO_INCREMENT' behaves depending on the 'NO_AUTO_VALUE_ON_ZERO' SQL mode: *note sql-mode::.
How to use the 'LAST_INSERT_ID()' function to find the row that contains the most recent 'AUTO_INCREMENT' value: *note information-functions::.
Setting the 'AUTO_INCREMENT' value to be used: *note server-system-variables::.
*note innodb-auto-increment-handling::
'AUTO_INCREMENT' and replication: *note replication-features-auto-increment::.
Server-system variables related to 'AUTO_INCREMENT' ('auto_increment_increment' and 'auto_increment_offset') that can be used for replication: *note server-system-variables::.
File: manual.info.tmp, Node: apache, Prev: examples, Up: tutorial