例如我有1个问题和5个答案.
题:
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 |
我想随机化ans1,ans2,ans3,ans4和correctAns的输出.
可能吗?如果是请指导我..谢谢
结构说明
虽然这个问题可能很有意思,但用例来解答问题和提供的结构是非常糟糕的.那是因为你被限制在一定数量的答案.这意味着:
>所有问题都必须有所有答案.如果一个问题应该只有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;
+---------------------------------------------------------------+ | 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)