"Rand()"的语法错误； Delphi中MySQL中的函数 [英] Syntax error with "Rand()" function in MySQL in Delphi

问题描述

我试图在MySQL中使用Rand()函数选择随机记录，但在Rand()LIMIT行中遇到语法错误

I am trying to use the Rand() function within MySQL to select a random record but I am getting a syntax error in the Rand() LIMIT line

qryCards.SQL.Add('SELECT * FROM tblCards WHERE Card_Rarity = "Epic"');
                  qryCards.Open;
                  iCount := qryCards.RecordCount;
                  qryCards.Close;
                  qryCards.SQL.Clear;
                  qryCards.SQL.Add('SELECT * FROM tblCards ORDER BY rand(' + IntToStr(iCount) + ') LIMIT 1');
                  qryCards.Open;
                  ShowMessage(qryCards.FieldByName('Card_Name').AsString);

推荐答案

首先，您没有正确使用RAND().它返回一个十进制数字0 <= N < 1.输入值是种子，而不是您期望的上限.要获得0 <= N < Count之间的随机整数，您必须将结果乘以RAND()*Count，这是您没有做的.但是您不需要这样做，您可以单独使用RAND()，无需先查询记录数:

First off, you are not using RAND() correctly. It returns a decimal number 0 <= N < 1. The input value is a seed, not an upper limit like you are expecting. To get a random integer number between 0 <= N < Count, you have to multiple the result, ie RAND()*Count, which you are not doing. But you don't need to do that, you can just use RAND() by itself, there is no need to query the record count first:

qryCards.SQL.Text := 'SELECT * FROM tblCards WHERE Card_Rarity = "Epic" ORDER BY RAND() LIMIT 1';
qryCards.Open;
ShowMessage(qryCards.FieldByName('Card_Name').AsString);

否则，您可以通过指定LIMIT子句的偏移量来选择随机记录，例如:

Otherwise, you can select a random record by specifying an offset to the LIMIT clause, eg:

qryCards.SQL.Text := 'SELECT * FROM tblCards WHERE Card_Rarity = "Epic"';
qryCards.Open;
iCount := qryCards.RecordCount;
qryCards.Close;
qryCards.SQL.Text := 'SELECT * FROM tblCards WHERE Card_Rarity = "Epic" LIMIT ' + IntToStr(Random(iCount)) + ', 1');
qryCards.Open;
ShowMessage(qryCards.FieldByName('Card_Name').AsString);

如果您的表具有自动增量id字段，且其中没有空格，则可以使用RAND()的其他方法.有关示例，请参见 MySQL选择随机记录.

If your table has an auto-increment id field with no gaps in it, there are other techniques you can use RAND() with. See MySQL Select Random Records for examples.

这篇关于"Rand()"的语法错误； Delphi中MySQL中的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！