MYSQL一些不常用的函数笔记

字符串替换

REPLACE ( fieldname, search, replace )

字符串截取

substring

length()： 单位是字节，utf8编码下,一个汉字三个字节，一个数字或字母一个字节。gbk编码下,一个汉字两个字节，一个数字或字母一个字节。 char_length()：单位为字符，不管汉字还是数字或者是字母都算是一个字符。

SELECT substring(poetry,1,length(poetry)-1) FROM `bless_lot`
SELECT substring(poetry,1,char_length(poetry)-1) FROM `bless_lot`

带有中文的截取应该使用 charlength，因为substring切的时候按照字符位数切

数据去重

DELETE ...

DELETE FROM table_copy 
WHERE key IN ( 
    SELECT t.key FROM (
    SELECT key FROM table_copy GROUP BY key having count(*) >1
    ) t
) 
AND id NOT IN(
    SELECT t.id FROM (
    SELECT id FROM table_copy GROUP BY key having count(*) >1
    ) t
)