**1、 mysql中将null值转换为0的语句** `update table set field1= '0' WHERE field1 IS NULL` **2、 mysql查看所有 | 指定数据库各表容量大小** ``` SELECT table_schema "Database Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) "Database Size in MB" FROM information_schema.tables GROUP BY table_schema ORDER BY SUM(data_length + index_length) DESC; select table_schema as '数据库', table_name as '表名', table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容量(MB)', truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema='kite' order by table_rows desc, index_length desc; ``` **3、批量替换CHARACTER 和 COLLATE** ``` ALTER TABLE `<表名>` CONVERT TO CHARACTER SET <新CHARSET> COLLATE <新COLLATE>; ``` **4、批量替换空格** ``` 其中，`CHAR(9)`是制表符（Tab）的ASCII码，`CHAR(10)`是换行符（LF）的ASCII码，`CHAR(13)`是回车符（CR）的ASCII码。 UPDATE mobile SET mobile = REPLACE ( REPLACE ( REPLACE ( REPLACE (mobile, CHAR(9), ''), CHAR (10), '' ), CHAR (13), '' ), ' ', '' ); ``` **5、导出csv** ``` SHOW VARIABLES LIKE 'secure_file_priv'; #修改配置 vi /etc/my.conf [mysqld] secure-file-priv=/tmp 本地 SELECT member_id as account,domain_id as biz_id,domain as biz_entity,created_at as op_time INTO OUTFILE '/tmp/mysql/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM domain_member_nsg LIMIT 10; 远程 mysql -h 110.80.136.249 -u root -p -P 3306 -e "USE dns_db;SELECT member_id as account,domain_id as biz_id,domain as biz_entity,created_at as op_time INTO OUTFILE '/tmp/file.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM domain_member_nsg LIMIT 10;" ``` **6、设置字符集的排序规则** ``` ALTER TABLE website_default_stat CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ```