一些常用的又复杂的SQL收录

1.增加，修改，删除表字段相关存储过程

# 增加
drop procedure if exists AddColumnUnlessExists;
create procedure AddColumnUnlessExists(
    IN dbName tinytext,
    IN tableName tinytext,
    IN fieldName tinytext,
    IN fieldDef text)
begin
    IF NOT EXISTS (
        SELECT * FROM information_schema.COLUMNS
        WHERE column_name=fieldName
        and table_name=tableName
        and table_schema=dbName
        )
    THEN
        set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
            ' ADD COLUMN ',fieldName,' ',fieldDef);
        prepare stmt from @ddl;
        execute stmt;
    END IF;
end;

# 修改
drop procedure if exists AlterColumnUnlessExists;
create procedure AlterColumnUnlessExists(
    IN dbName tinytext,
    IN tableName tinytext,
    IN fieldNameOld tinytext,
		IN fieldNameNew tinytext,
    IN fieldDef text)
begin
    IF EXISTS (
        SELECT * FROM information_schema.COLUMNS
        WHERE column_name=fieldNameOld
        and table_name=tableName
        and table_schema=dbName
        )
    THEN
        set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
            ' CHANGE COLUMN ',fieldNameOld,' ', fieldNameNew,fieldDef);
        prepare stmt from @ddl;
        execute stmt;
    END IF;
end;

# 删除
drop procedure if exists DropColumnUnlessExists;
create procedure DropColumnUnlessExists(
    IN dbName tinytext,
    IN tableName tinytext,
    IN fieldName tinytext)
begin
    IF EXISTS (
        SELECT * FROM information_schema.COLUMNS
        WHERE column_name=fieldName
        and table_name=tableName
        and table_schema=dbName
        )
    THEN
        set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
            ' DROP COLUMN ',fieldName);
        prepare stmt from @ddl;
        execute stmt;
    END IF;
end;

2.按一个表的分组来更新另一个表

update tableD as d
left join(select pid, count(*) as dcount from tableA GROUP BY pid) a
on a.pid = d.id
SET d.count = a.dcount

3.关键词替换

update test_tb set address=replace(address,'东','西') where id=2

-- update mysite_article_details set content=replace(content,'class="language-javascript"','class="line-numbers language-javascript"')

-- update mysite_cms_discussion set content=replace(content,'class="lang-js "','class="line-numbers lang-js"')
-- update mysite_cms_discussion set content=replace(content,'class="lang-js"','class="line-numbers lang-js"')
-- update mysite_cms_discussion set content=replace(content,'snippet-code-js','line-numbers')
-- update mysite_cms_discussion set content=replace(content,'snippet-code-html','line-numbers')

-- update mysite_cms_answer set content=replace(content,'class="lang-js "','class="line-numbers lang-js"')
-- update mysite_cms_answer set content=replace(content,'class="lang-js"','class="line-numbers lang-js"')
-- update mysite_cms_answer set content=replace(content,'snippet-code-js','line-numbers')
-- update mysite_cms_answer set content=replace(content,'snippet-code-html','line-numbers')

把多行的同一列合成一个字段：
SELECT GROUP_CONCAT(id) AS user_ids FROM broker_company WHERE `status`=1
-- 789,1516,1554,1584,1634,1714,1734,1742,1758,1834,1836,2076,...,9021,    //后边还有，但是只截取前1024个字符
设置如下
SET GLOBAL group_concat_max_len=4294967295;
SET SESSION group_concat_max_len=4294967295;