sorted by: recent | see : popular

Content Tagged with Gotchas + MySQL

Time for another MySQL Community Quiz:

Everybody knows that LAST_INSERT_ID() returns the value most recently created in an auto_increment column. Fewer of us know that you can pass a value to LAST_INSERT_ID(), and that value will be returned for the next call to LAST_INSERT_ID(). For example,

INSERT INTO table (someNonAutoIncrementField) VALUES (LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

The INSERT inserts ‘42′ into the table, and the SELECT returns 42.

So, here’s the question: What if we pass an expression to LAST_INSERT_ID and also use an auto_increment field in the same statement?

CREATE TABLE test (id int auto_increment primary key, field int);
INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

Show Answer ▼

mysql> INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

And for a follow up, does it matter if you swap the insertion around?

INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
SELECT LAST_INSERT_ID();

Show Answer ▼

mysql> INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

The manual warns about mixing LAST_INSERT_ID() and LAST_INSERT_ID(expr). This may fall under that same caveat.

First, the setup:

CREATE TABLE `user` (
  `user` varchar(255) DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL
);

INSERT INTO `user`
	(`user`, `host`, `sort`)
VALUES
	('','%',8),
	('testUser','%',7),
	('','%localhost',9),
	('testUser','%localhost',5),
	('','%localhost%',10),
	('testUser','%localhost%',6),
	('','localhost',2),
	('testUser','localhost',1),
	('','localhost%',4),
	('testUser','localhost%',3);

Now, the quiz:

SELECT * FROM user ORDER BY ___

Fill in the blank to get the following output. Difficulty: the `sort` field may not appear anywhere in the query.

+----------+-------------+------+
| user     | host        | sort |
+----------+-------------+------+
| testUser | localhost   |    1 |
|          | localhost   |    2 |
| testUser | localhost%  |    3 |
|          | localhost%  |    4 |
| testUser | %localhost  |    5 |
| testUser | %localhost% |    6 |
| testUser | %           |    7 |
|          | %           |    8 |
|          | %localhost  |    9 |
|          | %localhost% |   10 |
+----------+-------------+------+

I have a table of maps:

CREATE TABLE map (
	mapName varchar(255),
	mapType ENUM('continent', 'region', 'country'),
	continent varchar(255) NULL,
	region varchar(255) NULL,
	country varchar(255) NULL
);
INSERT INTO map VALUES
	('Europe', 'continent', 'Europe', NULL, NULL),
	('North America', 'continent', 'North America', NULL, NULL),
	('Northern Hemisphere', 'region', NULL, 'Northern Hemisphere', NULL),
	('Sweden', 'country', NULL, NULL, 'Sweden'),
	('Mexico', 'country', NULL, NULL, 'Mexico');

And a table of locations:

CREATE TABLE locations (
	locationId int auto_increment primary key,
	continent varchar(255),
	region varchar(255),
	country varchar(255)
);

INSERT INTO locations VALUES
	(NULL, 'Europe', 'Northern Hemisphere', 'Sweden'),
	(NULL, 'North America',  'Northern Hemisphere', 'Canada'),
	(NULL, 'North America',  'Pacific Northwest', 'USA');

Obviously, this example is denormalized - in the real world, I’d use a surrogate key and store continentId, regionId, and countryId. Even then, it’s not the ideal layout for this application. But that’s just part of the challenge.

Here’s the goal: find the most specific map for each location. By most specific, I mean use a country map if available. If not, use a region map. Use a continent map as a last resort.

So, the result set should be:

+------------+---------------------+
| locationId | mapName             |
+------------+---------------------+
|          1 | Sweden              |
|          2 | Northern Hemisphere |
|          3 | North America       |
+------------+---------------------+

Bonus points if it works pre-4.1

Hint ▼

Show Answer ▼

SELECT
	locationId,
	m.mapName
FROM
	locations l
	JOIN map m ON (
		l.continent = m.continent
		OR l.region = m.region
		OR l.country = m.country
	)
	LEFT JOIN map m2 ON (
		(l.continent = m2.continent
		OR l.region = m2.region
		OR l.country = m2.country)
		AND CAST(m.mapType AS unsigned) 

From a question on Freenode.

SELECT * FROM theTable;

+----+----------+
| id | data     |
+----+----------+
|  1 | middle   |
|  2 | first    |
|  3 | showLast |
+----+----------+

Fair enough. Let’s order it by `data`.

SELECT * FROM theTable ORDER BY data;

+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

What??? Maybe the server didn’t quite understand.

SELECT * FROM theTable ORDER BY data ASC;

+----+----------+
| id | data     |
+----+----------+
|  3 | showLast |
|  1 | middle   |
|  2 | first    |
+----+----------+

How did that happen? I assure you I have not switched to some ‘reverse order’ collation.

Show Answer ▼

SHOW CREATE TABLE theTable;

CREATE TABLE `thetable` (
  `id` int(11),
  `data` enum('showLast','middle','first')
)