MYSQL 利用concat函数 生成更新或者插入SQL

有时候需要批量运维一批数据，一条一条写SQL比较麻烦，可以使用下面的方法批量生成

select sales_order_number,
       a.sog_line_id,
       actual_price,
       sales_goods_unit_price,
       b.id,
       concat('update your_table set actual_price=', sales_goods_unit_price, ' where id=',
              b.id, ' and sog_line_id=', a.sog_line_id, ';', '-- ', sales_order_number) as updateSql
from table_a a
         left join table_b b on a.sog_line_id = b.sog_line_id
where sales_order_number in (
    ''
    );


select so.sales_order_number,
       so.so_head_id,
       concat('INSERT INTO your_table (so_head_id, sog_line_id, sku_id,actual_price,item_discount,
unit_price,quantity, biz_id,currency,price_verification_status) VALUES (', so.so_head_id, ',', sog.sog_line_id, ',',
              sog.sales_goods_code, ',',
              sog.sales_goods_unit_price, ',', ifnull(sog.sales_goods_discount_price, 'null'), ',',
              ifnull(sog.sales_goods_unit_price, 'null'),
              ',', 1, ',', 12, ',', '''USD''', ',', 0, ')', ';') as insertsql
from table_a so
         join table_b sog on so.so_head_id = sog.so_head_id
where sog.so_head_id in
      (xxxxxx, xxxxxx);

select sales_order_number,
       a.sog_line_id,
       actual_price,
       sales_goods_unit_price,
       b.id,
       concat('update your_table set actual_price=', sales_goods_unit_price, ' where id=',
              b.id, ' and sog_line_id=', a.sog_line_id, ';', '-- ', sales_order_number) as updateSql
from table_a a
         left join table_b b on a.sog_line_id = b.sog_line_id
where sales_order_number in (
    ''
    );


select so.sales_order_number,
       so.so_head_id,
       concat('INSERT INTO your_table (so_head_id, sog_line_id, sku_id,actual_price,item_discount,
unit_price,quantity, biz_id,currency,price_verification_status) VALUES (', so.so_head_id, ',', sog.sog_line_id, ',',
              sog.sales_goods_code, ',',
              sog.sales_goods_unit_price, ',', ifnull(sog.sales_goods_discount_price, 'null'), ',',
              ifnull(sog.sales_goods_unit_price, 'null'),
              ',', 1, ',', 12, ',', '''USD''', ',', 0, ')', ';') as insertsql
from table_a so
         join table_b sog on so.so_head_id = sog.so_head_id
where sog.so_head_id in
      (xxxxxx, xxxxxx);

concat怎么给查出来的字段加上单引号

在SQL中，CONCAT 函数通常用于将多个字符串连接成一个字符串。如果你需要给 CONCAT 的结果字段添加单引号，你可以在字段外部使用单引号来实现。

例如，假设你有一个名为 users 的表，其中包含 first_name 和 last_name 两个字段，你可以使用以下SQL语句来连接这两个字段，并在连接后的字符串两侧加上单引号：

SELECT CONCAT('\'', first_name, ' ', last_name, '\'') AS full_name FROM users;