mysql中用%name%+(num)递增字符串

发布于 2024-12-03 22:47:46 字数 218 浏览 1 评论 0原文

有没有办法用mysql实现这个算法，而不需要100500次查询和大量资源？

if (exists %name% in table.name) {
    num = 2;
    while(exists %newname%+(num) in table.name) num++;
    %name% = newname+(num);
}

谢谢

原文

Is there way to realize this algorithm with mysql without 100500 queries and lots of resources?

if (exists %name% in table.name) {
    num = 2;
    while(exists %newname%+(num) in table.name) num++;
    %name% = newname+(num);
}

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

场罚期间 2024-12-10 22:47:46

我不知道在 MySql 中使用存储过程可以做得好多少，但绝对可以比 100500 个查询做得更好：

SELECT name FROM table WHERE name LIKE 'somename%' ORDER BY name DESC LIMIT 1

此时，您知道可以增加 name 并且结果将不被使用。

我掩盖了一些细则（这种方法永远不会找到并填补命名方案中可能存在的任何“漏洞”，并且仍然不能保证该名称由于竞争条件而可用），但实际上它可以可以很容易地工作。

I don't know how much better you can do with a stored procedure in MySql, but you can definitely do better than 100500 queries:

SELECT name FROM table WHERE name LIKE 'somename%' ORDER BY name DESC LIMIT 1

At that point, you know that you can increment the number at the end of name and the result will be unused.

I 'm glossing over some fine print (this approach will never find and fill any "holes" in the naming scheme that may exist, and it's still not guaranteed that the name will be available due to race conditions), but in practice it can be made to work quite easily.

回复收藏 0 原文

南渊 2024-12-10 22:47:46

我能想到的最简单的方法是创建一个连续数字表
然后交叉连接......

SELECT a.name,b.id 
FROM table a
WHERE a.name = 'somename'
CROSS JOIN atableofsequentialnumbers b
WHERE NOT EXISTS (SELECT 1 FROM table x WHERE x.name = CONCAT(a.name,b.id))
LIMIT 10

这将返回前 10 个可用的号码/名称

The simpliest way I can see of doing it is to create a table of sequential numbers
then cross join on to it....

SELECT a.name,b.id 
FROM table a
WHERE a.name = 'somename'
CROSS JOIN atableofsequentialnumbers b
WHERE NOT EXISTS (SELECT 1 FROM table x WHERE x.name = CONCAT(a.name,b.id))
LIMIT 10

This will return the first 10 available numbers/names

回复收藏 0 原文

~没有更多了~