5+5=?

Answer:

a. 3
b. 4
c. 5
d. 9
e. 10

id  |  question  |  ans1  |  asn2  |  ans3  | ans4  |  correctAns  |
1   |  5+5       |    3   |    4   |    5   |   9   |      10      |

结构说明

虽然这个问题可能很有意思,但用例来解答问题和提供的结构是非常糟糕的.那是因为你被限制在一定数量的答案.这意味着：

>所有问题都必须有所有答案.如果一个问题应该只有2个选择的答案,那么 – 是的,将有N-2个NULL值(这里N表示答案数,在N = 4以上的情况下)
>如果只有一个问题必须有超过N个答案 – 那么是的,ALTER等待.对于所有其他问题,这将产生更多的NULL.

我建议改变你的结构.将答案存储在单独的表中,并附有外键到父级问题表.这将是足够的(除此之外,随机选择的问题会更容易解决)

问题解决方案

然而,涉及表结构的问题可能是有趣的,在MysqL中,这些问题通过INFORMATION_SCHEMA元数据进行了经典的解决.要解决sql问题,您需要使用prepared statements.这里是示例表：

MysqL> SELECT * From test;
+------+----------+------+------+------+------+------------+
| id   | question | ans1 | ans2 | ans3 | ans4 | correctAns |
+------+----------+------+------+------+------+------------+
|    1 | 5+5      |    3 |    4 |    5 |    9 |         10 |
+------+----------+------+------+------+------+------------+
1 row in set (0.00 sec)

现在,我假设列id和问题总是包含在第一和第二个结果中.其他列应随机分配.要做到这一点,可以使用INFORMATION_SCHEMA.COLUMNS表：

SELECT 
  CONCAT(
    'SELECT id,question,',GROUP_CONCAT(COLUMN_NamE),' From test') AS s 
FROM 
  (SELECT 
    COLUMN_NAME 
  FROM 
    INFORMATION_SCHEMA.COLUMNS 
  WHERE 
    TABLE_NAME='test' 
    AND 
    TABLE_SCHEMA='test' 
    AND 
    COLUMN_NAME NOT IN ('id','question') 
  ORDER BY RAND()) AS randCols;

您可以看到,这个sql将产生另一个sql.结果将如下：

+---------------------------------------------------------------+
| s                                                             |
+---------------------------------------------------------------+
| SELECT id,ans1,correctAns,ans4,ans3 FROM test |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

所以这可以很容易地在准备好的语句中使用.准备变量：

MysqL> set @s=(SELECT CONCAT('SELECT id,' FROM test') AS s FROM (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='test' AND TABLE_SCHEMA='test' AND COLUMN_NAME NOT IN ('id','question') ORDER BY RAND()) AS randCols);
Query OK,0 rows affected (0.01 sec)

用于声明：

MysqL> PRepare stmt from @s;
Query OK,0 rows affected (0.00 sec)
statement prepared

最后,执行它：

MysqL> execute stmt;
+------+----------+------------+------+------+------+------+
| id   | question | correctAns | ans1 | ans2 | ans4 | ans3 |
+------+----------+------------+------+------+------+------+
|    1 | 5+5      |         10 |    3 |    4 |    9 |    5 |
+------+----------+------------+------+------+------+------+
1 row in set (0.00 sec)