MySQL表：如何从所有字段中删除特定字符？
每周，我都必须完全替换几个非常大的 MySQL 表中的数据。因此，我通常所做的就是删除现有数据，导入新数据，然后运行常用查询以根据需要修改新数据。
不幸的是，这些天我注意到新数据包含不需要的字符，例如引号和多余的空格。其中一些表中有超过 100,000 条记录（据我所知），我无法在导入之前轻松地在记事本中打开数据以删除不需要的字符。
我意识到我可以为每个表中的每个列编写一个单独的查找和替换查询，如下所示：
UPDATE mytablename SET mycolumn = REPLACE(mycolumn, '"', '');
但是必须命名每个列很麻烦。无论如何，我想找到一个更优雅的解决方案。今天，我在互联网上发现了一个片段，看起来像是一个开始：
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
table_name = 'myTable' and ordinal_position = 1
我认为下一步可能是循环遍历序数位置，然后替换并更新每一列，但我不知道如何在 MySQL 中执行此操作。我也不知道如何在到达最后一列后停止循环，以避免出现错误消息。
有没有简单的方法可以做到这一点？还是我希望太多？
我是初学者，所以如果能有一个清晰、简单的解释将不胜感激。
提前致谢。
更多信息：
- 自从我发表第一篇文章以来，我发现我的服务器上不允许存储过程。太糟糕了。
- 不管怎样，我已经尝试了这个新代码，只是为了开始：
set @mytablestring='mytable';
设置@mycolumnnumber=1；
设置@mycolumnname =（从INFORMATION_SCHEMA.COLUMNS中选择column_name，其中table_name = @mytablestring和ordinal_position = @mycolumnnumber）；
从 mytable 中选择@mycolumnname；
不幸的是，在最终的 SELECT 查询中，@mycolumnname 被解释为字符串，而不是列名。所以查询不起作用。如果我能克服这个问题，我相信我可以编写一些代码通过递增 @mycolumnnumber 来循环遍历列。
如果有人知道如何解决这个问题，我将非常感谢一些帮助。
非常感谢。
Every week, I have to completely replace the data in several very large MySQL tables. So what I normally do is delete the existing data, import the new data, and then run my usual queries to modify the new data as needed.
Unfortunately, these days I have noticed that the new data contains unwanted characters, such as quotes and extra spaces. With well over 100,000 records in some of these tables (AFAIK), I cannot easily open the data in notepad to strip out unwanted characters, prior to importing.
I realize I could write a separate find and replace query for every single column in every table, like this:
UPDATE mytablename SET mycolumn = REPLACE(mycolumn, '"', '');
But having to name every column is a bother. Anyway, I would like to find a more elegant solution. Today, I found a snippet on the internet that looks like a start:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
table_name = 'myTable' and ordinal_position = 1
I think the next step might be to loop through the ordinal positions, and then replace and update each column, but I don't know how to do this in MySQL. I also don't know how to stop the loop after the last column is reached, to avoid error messages.
Is there an easy way to do this? Or am I hoping for too much?
I am a beginner, so a clear, simple explanation would be much appreciated.
Thanks in advance.
MORE INFORMATION:
- Since my first post, I have discovered that stored procedures are not allowed on my server. Too bad.
- Anyway, I have tried this new code, just to get started:
set @mytablestring='mytable';
set @mycolumnnumber=1;
set @mycolumnname=(SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @mytablestring and ordinal_position = @mycolumnnumber);
SELECT @mycolumnname FROM mytable;
Unfortunately, in the final SELECT query, @mycolumnname is interpreted as a string, not as a column name. So the query does not work. If I could get past this, I believe I could write some code to loop through the columns by incrementing @mycolumnnumber.
If anyone knows how to solve this, I would really appreciate some help.
Many thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我建议您看一下 vim、sed、awk 以及您可以在 Linux 上找到的许多其他文本编辑器和文本处理实用程序（有时也在 Windows 上找到）。对于记事本来说，100,000 条记录可能是一件痛苦的事情，但对于真正的文本处理实用程序来说却是小菜一碟。
例如，从
foobar.txt中删除所有#字符：或者，与在 (g)vim 中打开的文件相同：
I suggest that you take a look at vim, sed, awk and many of the other text editors and text processing utilities that you can find on Linux (and sometimes on Windows too). 100,000 records may be a pain in Notepad, but it's a piece of cake for real text processing utilities.
For example, to strip all
#characters fromfoobar.txt:Or, the same thing with the file opened in (g)vim: