I have a query that behaves differently in every version of MySQL I’ve tried. Could you do me a favor? Run the following four lines and post a comment with your results. And if you know of a particular bug fix that addresses this issue, let me know about that too.
CREATE TABLE `test` (`id` tinyint); INSERT INTO test VALUES (1), (2), (3), (4), (5); SHOW VARIABLES LIKE 'version%'; (SELECT * from test ORDER BY id DESC LIMIT 3) ORDER BY id ASC;
It’s so common to set the auto_increment column to primary key, it has become common belief that it’s required. Not true; an auto_increment column can use any index type. It doesn’t even have to be unique:
CREATE TABLE testTable ( id INT AUTO_INCREMENT, INDEX(id) ) ENGINE=MyISAM; -- Works for InnoDB too. INSERT INTO testTable VALUES (1), (1), (NULL); SELECT * FROM testTable; +----+ | id | +----+ | 1 | | 1 | | 2 | +----+
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.
A bit of a quiz to see if you’re paying attention:
First, we’ll set up a couple tables with some auto increment fields:
CREATE TABLE t1 (id INT AUTO_INCREMENT, INDEX(id)); CREATE TABLE t2 (id INT AUTO_INCREMENT, refT1 INT, INDEX(id));
Next, we’ll insert a row to one table:
INSERT INTO t1 VALUES (NULL);
Then, we’ll see what happens when we insert two rows using LAST_INSERT_ID():
INSERT INTO t2 VALUES (LAST_INSERT_ID(), 1), (LAST_INSERT_ID(), 2);
What happens here? Did the insert go ok, or did we get an error? What happens if we now issue
SELECT * FROM t2;
No doubt everybody has solved it before, but ten seconds on Google didn’t show any obvious results, so here we go: How to pick the top 5 records of each group.
(more…)
Can MySQL handle a staging table, one where a fixed number of rows are reused in a round-robin basis (e.g., temporarily store AJAX-driven auto-saved forms, without overwriting the real values so the user can still abandon changes), without an external script?
First thoughts turned to events, triggers, stored procedures, and such toys in the most recent versions of MySQL – none of which worked quite right. Turns out we can do it with a little one-time setup and a single statement that will work as far back as version 4.0.4.
(more…)
Trying to sum the top 5 entries in each group.
SELECT
theId,
SUM(theData) FROM (
(SELECT
@count := 0,
@oldId := 0 AS theId,
0 AS theData)
UNION
(SELECT
@count := if(@oldId = id, @count+1, 0),
@oldId := id,
if(@count < 5, a, 0)
FROM
(SELECT * FROM theTable ORDER BY id, a DESC) AS theTable
)
) AS theView
WHERE theId != 0
GROUP BY theId;
Table population:
INSERT INTO `thetable` VALUES
(1,1), (1,2), (1,3), (1,4),
(1,5), (1,6), (1,7), (2,1),
(2,2), (2,3), (2,4), (2,5);
Straight quotes can be used like backticks around aliases IN SOME CASES (as of 5.0.15, at least). Observe:
CREATE TABLE myTable (id int, someData int);
INSERT INTO myTable VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2);
These three queries produce identical output:
SELECT * FROM myTable GROUP BY id;
SELECT * FROM myTable GROUP BY `id`;
SELECT * FROM myTable GROUP BY 'id';
(more…)
Imagine you’re a rat in a scientific lab. There are two doors leading into your cage, one red, one blue. Every morning, the red door opens, and a bit of cheese tumbles in. Sometimes you can eat the cheese, and all is well. Other times, there’s a long silver thing stuck in it. You find that if you eat the cheese where it lies on the metal cage floor, it makes your tongue hurt, your paw jump, and gives you a very bad headache. (more…)
Here are a couple table definitions:
CREATE TABLE `users1` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);
CREATE TABLE `users2` (
`name` varchar(10) default NULL,
`birthday` date default NULL
);
After inserting some data, we get the following row counts:
SELECT COUNT(*) FROM users1;
+----------+
| COUNT(*) |
+----------+
| 15 |
+----------+
SELECT COUNT(*) FROM users2;
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+
Now, for this query:
SELECT * FROM users1 UNION SELECT * FROM users2;
How many rows will be in the result set? (Choose the best answer)
a) Exactly 27
b) At least 12
c) At least 15
d) Between 1 and 27
e) Zero or more
Powered by WordPress