mysql> SELECT c1, c2 FROM t WHERE c2 LIKE 'a%';
+------+------+
| c1   | c2   |
+------+------+
| a    | abc  |
+------+------+

Is the following construct for the LIKE clause legal?

SELECT c1, c2 FROM t WHERE c2 LIKE CONCAT('a', '%');

And how about this one?

SELECT c1, c2 FROM t WHERE c2 LIKE CONCAT(c1, '%');

Bonus question: Suppose the table has many rows, several more columns and there’s an index on (c2, c1). Could that index be utilized when running the legal versions of the query — and if so, how?

(more…)

This quiz originated on FreeNode#mysql, where someone asked how to count the number of newline-separated “fields” in a TEXT column. For the purposes of the quiz, I’ve changed the idea slightly but you should be able to appreciate the usefulness of this method for any x-separated data that you have to deal with.

Given the following data…

mysql> SELECT * FROM t;
+-----------+
| s         |
+-----------+
| aba       |
| abacad    |
| abacadaea |
+-----------+

…create a query which counts the number of occurrences of the character ‘a’ in each line, e.g.

mysql> SELECT s, <something> AS count_a
    -> FROM t;
+-----------+---------+
| s         | count_a |
+-----------+---------+
| aba       |       2 |
| abacad    |       3 |
| abacadaea |       5 |
+-----------+---------+

(more…)

“The ATTACK query is an undocumented feature of the development branch of MySQL…”

With the popularity of MySQL I guess it was just a question of time before someone came up with a game not just based on MySQL, but also with the theme of MySQL. In the words of the website…

(more…)

This wonderful quiz from Vladimir Kolesnikov is one of those that should make you stop and think for a moment…

Given this table and data:

mysql> SELECT * FROM t;
+------+------+
| i1   | i2   |
+------+------+
|    1 |    2 |
|    2 |    1 |
|    4 |    3 |
|    3 |    4 |
+------+------+
4 rows in set (0.00 sec)

What is the result of the following three statements?

SELECT * FROM t ORDER BY 1

SELECT * FROM t ORDER BY 2

SELECT * FROM t ORDER BY 1+1

(more…)

Today’s quiz question, which subsequently became three, comes courtesy of Janek Bogucki.

Since questions 2 and 3 contain hints on the other questions, they are embedded within the answers to previous questions.

Question 1: What happens if you throw the following statement at your MySQL server?

CREATE TABLE log(
name CHAR(20) NOT NULL,
count INT UNSIGNED NOT NULL
)

(more…)

This quiz on INFORMATION_SCHEMA is stolen (with permission!), from Roland:

• Specify a minimal set of columns of the information_schema.TABLE_CONSTRAINTS table that is sufficient to reliably identify a single row in the information_schema.TABLE_CONSTRAINTS table.
• Argue why these columns are necessary and sufficient to identify a row, and why a smaller set of columns does not exist

(more…)

What happens if you try to enter the following command into MySQL?

CREATE TABLE t1 (
i INT,
INDEX i (i)
);

(more…)

Here’s something I came across several months back. Watch in wonder as we create a PRIMARY KEY which is already there, then drop it again, only and to see that it’s still present in the table…:

mysql> DESC t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists
mysql> ALTER TABLE t1 ADD PRIMARY KEY(i);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

Explain (or simply read the answer to see) how that sequence of events is possible. (more…)

Thanks for the hard work, guys! It’s great to see that people are finding this worthwhile. I will try to get around to linking up the individual quizzes as soon as I find some time.

If you wish to translate the quizzes into your own language, please contact me at carsten (at) bitbybit (dot) dk before you start so we can sort out the details.