MySQL 不区分大小写的 REPLACE [英] Case Insensitive REPLACE for MySQL

问题描述

MySQL 是否有不区分大小写的Replace?

我正在尝试将用户的旧用户名替换为段落文本中的新用户名.

$targetuserold = "@".$mynewusername;$targetusernew = "@".$newusername;$sql = "更新时间表SET message = Replace(message,'".$targetuserold."', '".$targetusernew."')";$result = mysql_query($sql);

这遗漏了旧用户名不同的情况.示例:在我的数据库中的所有行中将Hank"替换为Jack"将留下hank"的实例.

解决方案

我的最终解决方案是我不能做不区分大小写的Replace.

不过，我确实找到了解决方法.

我试图提供一个用户可以更改其用户名的功能.然后，系统需要更新在数据库中所有消息中找到 @oldusername 的任何位置.

问题是...人们不会在 members 表中找到的正确情况下键入其他人的用户名.因此，当用户更改其用户名时，它不会捕获那些 @oldSeRNAmE 实例，因为它与 oldusername 的真实格式不匹配.>

我的 GoDaddy 共享服务器没有权限使用自定义 SQL 函数执行此操作，因此我必须找到不同的方法.

我的解决方案:在将新消息插入数据库后，每当在新消息中找到用户名时，我都会在此时使用 UPDATE 语句来替换用户名他们使用 members 表中的正确格式大小写输入.这样，如果该人将来想要更改他们的用户名，数据库中该用户名的所有实例都将是完全相同的大小写格式.问题解决了.

Is there a case insensitive Replace for MySQL?

I'm trying to replace a user's old username with their new one within a paragraph text.

$targetuserold = "@".$mynewusername;
$targetusernew = "@".$newusername;

$sql = "
    UPDATE timeline 
    SET message = Replace(message,'".$targetuserold."', '".$targetusernew."')
";

$result = mysql_query($sql);

This is missing the instances where the old username is a different case. Example: replacing "Hank" with "Jack" in all the rows in my database will leave behind instances of "hank".

解决方案

My solution ultimately was that I cannot do a case insensitive Replace.

However, I did find a workaround.

I was trying to have a feature where a user can change their username. The system would then need to update wherever @oldusername was found in all the messages in the database.

The problem was... people wouldn't type other people's usernames in the correct case that it is found in the members table. So when the user would change their username, it wouldn't catch those instances of @oldSeRNAmE because of it not matching the case of the real format of the oldusername.

I don't have permission with my GoDaddy shared server to do this with a customized SQL function, so I had to find a different way.

My solution: Upon inserting new messages into the database, whenever a username is found in the new message, I have an UPDATE statement at that point to replace the username they typed with the correct formatted case that is found in the members table. That way, if that person ever wants to change their username in the future, all the instances of that username in the database will all be the same exact formatted case. Problem solved.

这篇关于MySQL 不区分大小写的 REPLACE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！