August « 2006 « A Little Noise

August 31, 2006

Auto_increment Indexing

Filed under: MySQL FAQ — snoyes @ 5:15 pm

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 |
+----+

Comments (1)

August 23, 2006

Order By Puzzle

Filed under: MySQL Gotchas — snoyes @ 2:30 pm

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')
)

Ah. `data` is an enum. ORDER BY on an enum orders by the enum index, not the string. You can order by CONCAT(data) or by CAST(data AS char). Are there better ways?

Comments (5)

August 22, 2006

LAST_INSERT_ID and multi-row inserts

Filed under: MySQL Gotchas — snoyes @ 8:43 pm

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;

Show Answer ▼

No errors, because we defined the auto_increment field for t2 not as a primary or unique key, but just with index. Remember that LAST_INSERT_ID() only picks up the last value you inserted using NULL, and not explicitly defined values. So we get two copies of the value ‘1’ in our id field.

+----+-------+
| id | refT1 |
+----+-------+
|  1 |     1 | 
|  1 |     2 | 
+----+-------+

Comments (3)

August 21, 2006

Top N of a Group

Filed under: MySQL FAQ — snoyes @ 7:57 pm

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…)

Comments (10)

August 8, 2006

Staging Table

Filed under: MySQL — snoyes @ 1:58 pm

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…)

Comments (2)

August 1, 2006

Sum top N of a group

Filed under: MySQL FAQ — snoyes @ 3:48 pm

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);

Comments (0)