我在下面有一个表格,我的问题是我如何根据参数更新 mysql 中的列。
+---+------------+-------------+
| id| A | B |
+---+------------+-------------+
| 1 | a b a a | |
| 2 | b c a | |
| 3 | b d c | |
| 4 | a | |
+---+------------+-------------+
预期结果应计算"a"的出现次数,然后更新列B。 当然,我需要使用正则表达式来计算"a"的数量
+---+------------+-------------+
| | A | B |
+---+------------+-------------+
| 1 | a b a a | 3 |
| 2 | b c a | 1 |
| 3 | b d c | 0 |
| 4 | a | 1 |
+---+------------+-------------+
这可以在单个 SQL 语句中完成。(遗憾的是,此方法不能满足使用正则表达式的要求。编辑:请参阅下面的后续内容,了解使用正则表达式的不优雅和低效的方法。
我们可以获取第 A
列中字符 'a' 的出现次数,并将第 B
列设置为计数,查询如下:
UPDATE mytable t
SET t.B = ( CHAR_LENGTH(t.A) - CHAR_LENGTH(REPLACE(t.A,'a','')) )
让我们稍微解开一下。这是一个UPDATE
语句,没有WHERE
子句,因此我们将访问并可能更新mytable
中的每一行。(我们正在分配表别名t
。这不是必需的,但我们稍后将使用该别名限定对列A
和B
的引用,以使阅读 SQL 语句的人更清楚地知道这些是对表中列的引用。
在下一行,我们有一个简单的SET
子句,为列B
赋值。
这是下一个表达式,即我们分配给第 B
列的值,我们应该稍微解压缩一下。
要计算'a'
个字符,我们可以使用一个小技巧:我们知道'a'
字符的长度正好是一个字符。
"诀窍"是使用 REPLACE
函数,搜索字符'a'
的所有出现次数,并删除它们(用零长度字符串替换它们)。然后我们可以比较两个字符串的长度(字符数)。区别在于原始字符串中'a'
个字符的数量。
作为这些表达式如何工作的演示:
SELECT t.foo AS foo
, REPLACE(t.foo,'a','') AS foo_2
, CHAR_LENGTH(t.foo) AS len
, CHAR_LENGTH(REPLACE(t.foo,'a','')) AS len_2
, CHAR_LENGTH(t.foo) - CHAR_LENGTH(REPLACE(t.foo,'a','')) AS `len-len_2`
FROM ( SELECT 'a b a a' AS foo
UNION ALL SELECT 'b c a'
UNION ALL SELECT 'b c d'
UNION ALL SELECT 'a '
) t
从该查询返回的:
foo foo_2 len len_2 len-len_2
------- ------ ------ ------ ---------
a b a a b 7 4 3
b c a b c 5 4 1
b c d b c d 5 5 0
a 3 2 1
注意:返回的内容基本上是已删除字符数的计数。因此,如果我们想计算多个字符的字符串的出现次数,例如:cat
,我们需要考虑到这一点。
将返回值除以 cat
中的字符数将是实现此目的的一种方法。或者,我们可以将字符串cat
替换为长度为两个字符的字符串,例如 'xx'
,因此长度的差异将是每次出现一个字符。
随访
最初的问题是如何使用正则表达式计算"a"字符。我的第一个想法是MySQL REGEXP
是不可能的,因为返回的返回要么是NULL,0要么是1。但稍微考虑一下,如果我们计算到一些有限的出现次数,这是可以做到的。可以检查字符串是否至少包含一个"a"字符,这非常简单:
'a b a a' REGEXP 'a'
如果匹配,则返回 1,如果不匹配,则返回 0。还可以检查字符串是否至少包含两个"a"字符。这也很简单:
'a b a a' REGEXP 'a.*a'
如果我们将上述两个表达式的结果相加,我们可以得到 0、1 或 2 的"a"字符数。
我们可以重复相同的模式,将其扩展到匹配 3、4、5 等"a"字符。
它并不优雅,我们当然不想了解CPU在进行所有这些比较时会变得多么温暖。但它确实返回指定的结果,最多返回某个有限的最大计数。在此示例中,六个。(包含六个以上"a"字符的字符串将返回计数 6。
作为演示:
SELECT t.foo
, (t.foo REGEXP CONCAT('.*',REPEAT('a.*',1)))
+ (t.foo REGEXP CONCAT('.*',REPEAT('a.*',2)))
+ (t.foo REGEXP CONCAT('.*',REPEAT('a.*',3)))
+ (t.foo REGEXP CONCAT('.*',REPEAT('a.*',4)))
+ (t.foo REGEXP CONCAT('.*',REPEAT('a.*',5)))
+ (t.foo REGEXP CONCAT('.*',REPEAT('a.*',6)))
AS cnt_a
FROM ( SELECT 'a b a a' AS foo
UNION ALL SELECT 'b c a'
UNION ALL SELECT 'b c d'
UNION ALL SELECT 'a '
) t
选项 1:纯 SQL
将要计数的子字符串替换为空字符串。通过比较生成的字符串和原始字符串的长度,您可以知道有多少次出现:
update table set b = (length(a) - length(replace(a,'a',''))) / length('a')
可以将常量字符串'a'
替换为任意长度的任何字符串。
选项 2:使用 PHP 和 SQL
您可以使用 PHP 遍历所有行,并使用 substr_count
函数来计算子字符串的出现次数(在您的情况下,子字符串将是"a")。然后,更新该行中b
的值。假设字段id
是主键:
$query = $pdo->query("select id,a from table");
while($row = $query->fetch()) {
$b = substr_count($row['a']);
$id = $row['id'];
$pdo->query("update table set b = $b where id = $id");
}
请注意,此方法不是很有效。
UPDATE Table SET B = i.b
FROM (SELECT LENGTH(A) - LENGTH(REPLACE(A, 'a', '')) as b from Table ) i
WHERE i.ID = Table.ID