mysql concat函数使用方法

mysql拼接字符串函数

1.concat

mysql> select concat('drop table `', table_name, '`;') from information_schema.tables where table_schema not in ('mysql','sys') and engine='MyISAM';
+--------------------------------------------+
| concat('drop table `', table_name, '`;')   |
+--------------------------------------------+
| drop table `act_de_databasechangeloglock`; |
| drop table `act_de_model`;                 |
| drop table `act_de_model_history`;         |
| drop table `act_de_model_relation`;        |
| drop table `act_my_variable`;              |
| drop table `cnt_agent`;                    |
| drop table `cnt_audit_dept`;               |
| drop table `cnt_auditdept_config`;         |
| drop table `cnt_auditinfo`;                |
| drop table `cnt_auditsubdept_config`;      |
| drop table `cnt_common_audit`;             |
| drop table `cnt_flow_config`;              |
| drop table `cnt_partner_attach`;           |
| drop table `cnt_workflow`;                 |
| drop table `girl`;                         |
| drop table `hibernate_sequence`;           |
| drop table `mat_invoice_address`;          |
| drop table `mat_repertory`;                |
| drop table `mat_repertory_supplier`;       |
| drop table `oa_car`;                       |
| drop table `oa_car_maintain`;              |
| drop table `oa_car_use`;                   |
| drop table `oa_carpply`;                   |
| drop table `oa_leave`;                     |
| drop table `oa_meeting_apply`;             |
| drop table `oa_meeting_meno`;              |
| drop table `oa_meeting_room`;              |
| drop table `oa_scheduler`;                 |
| drop table `oa_topic_reply`;               |
| drop table `sys_logger_info`;              |
+--------------------------------------------+
30 rows in set (0.01 sec)



---不显示表格及不显示字段名
# mysql -uroot -p --silent -N -D platform-cssd
Enter password: 
mysql> select concat('drop table ', table_name, ';') from information_schema.tables where table_schema not in ('mysql','sys') and engine='MyISAM';

drop table act_de_databasechangeloglock;
drop table act_de_model;
drop table act_de_model_history;
drop table act_de_model_relation;
drop table act_my_variable;
drop table cnt_agent;
drop table cnt_audit_dept;
drop table cnt_auditdept_config;
drop table cnt_auditinfo;
drop table cnt_auditsubdept_config;
drop table cnt_common_audit;
drop table cnt_flow_config;
drop table cnt_partner_attach;
drop table cnt_workflow;
drop table girl;
drop table hibernate_sequence;
drop table mat_invoice_address;
drop table mat_repertory;
drop table mat_repertory_supplier;
drop table oa_car;
drop table oa_car_maintain;
drop table oa_car_use;
drop table oa_carpply;
drop table oa_leave;
drop table oa_meeting_apply;
drop table oa_meeting_meno;
drop table oa_meeting_room;
drop table oa_scheduler;
drop table oa_topic_reply;
drop table sys_logger_info;

2.concat_ws

mysql> select concat_ws(' ', 'Hello', user, '!') as greeting from mysql.user;
+--------------------------+
| greeting                 |
+--------------------------+
| Hello cloudimage !       |
| Hello imageuser !        |
| Hello nacos !            |
| Hello root !             |
| Hello root !             |
| Hello repl !             |
| Hello backup !           |
| Hello mysql.infoschema ! |
| Hello mysql.session !    |
| Hello mysql.sys !        |
| Hello nacos !            |
| Hello root !             |
+--------------------------+
12 rows in set (0.00 sec)