Question

MYSQL如何替换部分字符串

Answer 1

mysql>
select
host,user
from
user
where
user='user';
+-----------------------+----------+
|
host
|
user
|
+-----------------------+----------+
|
localhost.localdomain
|
testuser
|
+-----------------------+----------+
update字段host的内容，把"main"改成"slave",用REPLACE
mysql>
update
user
set
host=REPLACE(host,'main','slave')
where
user='user';
Query
OK,
1
row
affected
(0.00
sec)
Rows
matched:
1
Changed:
1
Warnings:
0
mysql>
select
host,user
from
user
where
user='user';
+------------------------+----------+
|
host
|
user
|
+------------------------+----------+
|
localhost.localdoslave
|
testuser
|
+------------------------+----------+

Answer 2

找了半天也没有找到可以实现这个功能的函数，
最后解决办法是：导入数据表，用awk处理里，再插入一个新表（数据结构一样）
mysql>select
*
from
表1
INTO
OUTFILE
'b.txt';
awk
-F'/'
'{print
$1"/"$2}'
b.txt
>a.txt
mysql>create
table
表2
like表1;
mysql>load
data
infile
'a.txt'
into
table
表2;
同时也坐着用函数或其它巧妙的办法来解决。