MySQL conditional SELECT statement

If there are records that have a field containing "X", return them, else 开发者_Python百科return a random record.

How the heck do you do this?

This is best done with 2 queries. The first returns the records where field='x'. If that's empty, then do a query for a random record with field!='x'. Getting a random record can be very inefficient as you'll see from the number of "get random record" questions on SO. Because of this, you really only want to do it if you absolutely have to.

Just the bit to select a random record would be very difficult and highly unefficient on large tables in mysql, in this website you can find one script to do it, it should be trivial to add your condition for 'x' and get the functionality you need.

Well, here is my example based on mysql.users table:

First, non existing records:

mysql> SELECT * FROM (select user, 1 as q from user where user like '%z' union all (select user, 0 from user limit 1)) b WHERE q=(SELECT CASE WHEN EXISTS(select user, 1 as q from user where user like '%z' ) THEN 1 ELSE 0 END);

+--------+---+
| user   | q |
+--------+---+
| drupal | 0 |
+--------+---+

1 row in set (0.00 sec)

Then, existing:

mysql> SELECT * FROM (select user, 1 as q from user where user like '%t' union all (select user, 0 from user limit 1)) b WHERE q=(SELECT CASE WHEN EXISTS(select user, 1 as q from user where user like '%t' ) THEN 1 ELSE 0 END);

+------------------+---+
| user             | q |
+------------------+---+
| root             | 1 |
| root             | 1 |
| debian-sys-maint | 1 |
| root             | 1 |
+------------------+---+

4 rows in set (0.00 sec)

Maybe it will be useful, or maybe someone will be able to rewrite it in better way.