在MYSQL数据库中，我需要使用介于8到120之间的随机数更新“people”表，但如果该值介于103到109之间，则希望它变为110。
如何执行此类查询？
如何执行此类查询？
UPDATE people SET column1 = '________random expression_________'
5个回答
2
我没有测试过，但也许它应该可以工作。
UPDATE people
SET column1 = (
SELECT if(r.rand BETWEEN 103 AND 109, 110, r.rand)
FROM ( SELECT floor(8+rand()*113) rand ) r
)
- Karolis
4
谢谢你的帮助，但我真的不理解公式中的SELECT部分（我看不到你在哪里指定最大值120）。 - Adam Strudwick
floor(8+rand()*113) 是一个介于 8 和 120 之间的随机整数。 - Karolis我刚刚测试了一下，这里出现的问题是：它给每一行都赋了相同的值，而我不想要这样 - 我希望每一行都不同。 - Adam Strudwick
1@AdamStrudwick请参考Martin的评论。但是使用113代替112，因为
rand()的范围是0<= rand() <1(从不等于1)。 - Karolis1
我编写了一个Perl脚本来运行SQL查询20k次，输出表明SELECT的值是正确的（显然，您只想从脚本中提取和调整SQL以更新您的表，但我包括脚本以便您可以证明它确实有效）：
#!/usr/bin/perl -w
use strict;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect( "dbi:mysql:test", "root", "" ) or die $!;
my $q = <<EOQ;
select if(r.bar between 103 and 109, 110, r.bar )
from ( select floor( rand() * 113 ) + 8 as bar ) r
EOQ
;
my $sth = $dbh->prepare( $q );
my $vals = {};
for ( 0 .. 20000 ) {
$sth->execute();
my $row = $sth->fetchrow_arrayref();
my $int = $row->[0];
$vals->{$int}++;
}
print join( "\n", sort keys %$vals ), "\n";
- Kenny
3
我刚刚测试了一下，发现问题是：它给每一行赋予了相同的值，而我不希望这样 - 我希望每一行都是不同的。 - Adam Strudwick
你会如何执行这样的查询？ - Adam Strudwick
通过多次执行 UPDATE？因此，您针对 people 表中的每个条目一遍又一遍地运行 'UPDATE people SET (blah) WHERE person = ?'。 - Kenny
1
你可以随时这样做
UPDATE people SET column1 = floor(8+rand()*113)
UPDATE people
SET column1 = 110
WHERE column1 BETWEEN 103 AND 109
或者另一种方式似乎有效，但我相信会被改进的更好。
UPDATE people
SET column1 =
(
SELECT rand FROM
(
SELECT 8 as rand UNION ALL
SELECT 9 as rand UNION ALL
SELECT 10 as rand UNION ALL
SELECT 11 as rand UNION ALL
SELECT 12 as rand UNION ALL
SELECT 13 as rand UNION ALL
SELECT 14 as rand UNION ALL
SELECT 15 as rand UNION ALL
SELECT 16 as rand UNION ALL
SELECT 17 as rand UNION ALL
SELECT 18 as rand UNION ALL
SELECT 19 as rand UNION ALL
SELECT 20 as rand UNION ALL
SELECT 21 as rand UNION ALL
SELECT 22 as rand UNION ALL
SELECT 23 as rand UNION ALL
SELECT 24 as rand UNION ALL
SELECT 25 as rand UNION ALL
SELECT 26 as rand UNION ALL
SELECT 27 as rand UNION ALL
SELECT 28 as rand UNION ALL
SELECT 29 as rand UNION ALL
SELECT 30 as rand UNION ALL
SELECT 31 as rand UNION ALL
SELECT 32 as rand UNION ALL
SELECT 33 as rand UNION ALL
SELECT 34 as rand UNION ALL
SELECT 35 as rand UNION ALL
SELECT 36 as rand UNION ALL
SELECT 37 as rand UNION ALL
SELECT 38 as rand UNION ALL
SELECT 39 as rand UNION ALL
SELECT 40 as rand UNION ALL
SELECT 41 as rand UNION ALL
SELECT 42 as rand UNION ALL
SELECT 43 as rand UNION ALL
SELECT 44 as rand UNION ALL
SELECT 45 as rand UNION ALL
SELECT 46 as rand UNION ALL
SELECT 47 as rand UNION ALL
SELECT 48 as rand UNION ALL
SELECT 49 as rand UNION ALL
SELECT 50 as rand UNION ALL
SELECT 51 as rand UNION ALL
SELECT 52 as rand UNION ALL
SELECT 53 as rand UNION ALL
SELECT 54 as rand UNION ALL
SELECT 55 as rand UNION ALL
SELECT 56 as rand UNION ALL
SELECT 57 as rand UNION ALL
SELECT 58 as rand UNION ALL
SELECT 59 as rand UNION ALL
SELECT 60 as rand UNION ALL
SELECT 61 as rand UNION ALL
SELECT 62 as rand UNION ALL
SELECT 63 as rand UNION ALL
SELECT 64 as rand UNION ALL
SELECT 65 as rand UNION ALL
SELECT 66 as rand UNION ALL
SELECT 67 as rand UNION ALL
SELECT 68 as rand UNION ALL
SELECT 69 as rand UNION ALL
SELECT 70 as rand UNION ALL
SELECT 71 as rand UNION ALL
SELECT 72 as rand UNION ALL
SELECT 73 as rand UNION ALL
SELECT 74 as rand UNION ALL
SELECT 75 as rand UNION ALL
SELECT 76 as rand UNION ALL
SELECT 77 as rand UNION ALL
SELECT 78 as rand UNION ALL
SELECT 79 as rand UNION ALL
SELECT 80 as rand UNION ALL
SELECT 81 as rand UNION ALL
SELECT 82 as rand UNION ALL
SELECT 83 as rand UNION ALL
SELECT 84 as rand UNION ALL
SELECT 85 as rand UNION ALL
SELECT 86 as rand UNION ALL
SELECT 87 as rand UNION ALL
SELECT 88 as rand UNION ALL
SELECT 89 as rand UNION ALL
SELECT 90 as rand UNION ALL
SELECT 91 as rand UNION ALL
SELECT 92 as rand UNION ALL
SELECT 93 as rand UNION ALL
SELECT 94 as rand UNION ALL
SELECT 95 as rand UNION ALL
SELECT 96 as rand UNION ALL
SELECT 97 as rand UNION ALL
SELECT 98 as rand UNION ALL
SELECT 99 as rand UNION ALL
SELECT 100 as rand UNION ALL
SELECT 101 as rand UNION ALL
SELECT 102 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 110 as rand UNION ALL
SELECT 111 as rand UNION ALL
SELECT 112 as rand UNION ALL
SELECT 113 as rand UNION ALL
SELECT 114 as rand UNION ALL
SELECT 115 as rand UNION ALL
SELECT 116 as rand UNION ALL
SELECT 117 as rand UNION ALL
SELECT 118 as rand UNION ALL
SELECT 119 as rand UNION ALL
SELECT 120 as rand
) T
ORDER BY rand()
LIMIT 1
)
- Martin Smith
2
3谁使用第二个的，应该受到肛门酷刑。 :) - ThiefMaster
@ThiefMaster - 我并不反对！我想到了使用用户变量的方法可能可行... - Martin Smith
1
update people
set `column1` = if ((@a := floor(8+rand()*113)) BETWEEN 103 AND 109 , 110, @a)
;
- Xploit
-1
在您的代码中生成随机数，并将其传递给数据库。数据库是为处理数据而设计的，不是执行此操作的理想场所。
- Kieren Johnstone
4
2所以我应该先执行一个SELECT语句，用PHP生成值，然后在每次循环中执行一个UPDATE语句。 - Adam Strudwick
困惑于这些负评。是的，这就是我们所说的“业务逻辑”。 - Kieren Johnstone
2一旦你想要对“所有数据”进行操作，将其放在数据库之外可能会很糟糕 - CPU 有 SIMD 等功能，而数据库允许一次更新一堆行是有原因的：它们可以更好地提高性能，因为它们知道相同的操作（“设置为随机值”）是针对多个/所有元素执行而不仅仅是单个元素。因此，坚持抽象模型（“数据库仅存储数据，不包含业务逻辑”）并不总是好的 - 并且使用存储过程将一些业务逻辑移入数据库可能是非常有效的事情。 - ThiefMaster
如果你开始谈论诸如指令集之类的事情，那么你暗示存在大量的数据或海量查询。除非这是一个密集型数据处理应用程序，否则我认为你应该从最好的意图/原则开始。在我看来，“快速简便”并不总是最好的选择。 - Kieren Johnstone
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 201 SQL UPDATE语句使用CONCAT附加字符串更新字段中的所有值无效。
- 3 基于另一现有行更新列的SQL UPDATE语句
- 12 在 SQL Server 中使用更新语句随机更新表中的记录？
- 4 合并SQL UPDATE语句
- 3 SQL Server update语句中使用where column in（重复值）-需要更新多少个记录？
- 3 使用C#中的if语句通过UPDATE更新特定行（SQL）
- 30 使用单个UPDATE SQL语句执行多个更新操作是否可行？
- 4 使用If When的SQL Update语句
- 17 使用pyodbc的SQL Update语句
- 3 SQL Server：使用随机值更新列
UPDATE people SET column1 = floor(8+rand()*112); UPDATE people SET column1 = 110 WHERE column1 BETWEEN 103 AND 109。 - Martin Smith