[Mysql]~常用

Mysql常用配置

max_binlog_cache_size(binlog 缓存大小设置)

在大批量update之类操作时，max_binlog_cache_size设置小会报错。

# 查询
show variables like '%max_binlog_cache_size%'
# 设置
set global max_binlog_cache_size= 536870912

secure_file_priv(LOAD DATA导出文件配置）

secure-file-priv参数是用来限制LOAD DATA, SELECT … OUTFILE, and LOAD_FILE()传到哪个指定目录的。

# 查询
show global variables like '%secure%';

设置secure-file-priv参数
当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

<!--  配置文件设置 -->
secure_file_priv = ''

skip-grant-tables(本地登录密码)

编辑/etc/my.cnf
在[mysqld]配置部分添加一行

skip-grant-tables

重启mysql

service mysql restart

重置密码

mysql -u root -p mysqlnew

Mysql表结构查询

根据数据库查询表名

SELECT
	table_name 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'pls';

根据表名查询字段、类型、备注

SELECT
	column_name,
	column_type,
	column_comment 
FROM
	information_schema.columns
WHERE
	table_name = 't_apply_pro';

Mysql常用语句

查询重复

select jz_eas_cust_id from t_ecif GROUP BY jz_eas_cust_id Having count(*)>1;

查询数据到新建表

CREATE TABLE t_customer_copy AS SELECT * FROM t_customer where LENGTH(custom_no) <18;

查询替换换行符

char(9) 水平制表符
char(10) 换行符
char(13) 回车符

SELECT REPLACE ( "换行符
	换行符", CHAR ( 13 ), 'aaa' )

Mysql导入导出

LOAD DATA

导入dat

-- 修改导入文件M_CI_CUST_CONS_JC_JZ_LIST_PER.dat的路径
LOAD DATA LOCAL INFILE 'D:\\M_CI_CUST_CONS_JC_JZ_LIST_PER.dat'
INTO TABLE t_ecif
-- 每个字段之间的分隔符
FIELDS TERMINATED BY ''
-- 每行分隔符
LINES TERMINATED BY '\n'
-- 要插入的字段名
(
  `cust_id`,
  `jz_cbs_cust_id`,
  `jz_cms_cust_id`,
  `jz_eas_cust_id`,
  `cust_type`,
  `cust_name`,
  `ident_type`,
  `ident_no` ,
  `main_iden_flaf`
);

导入csv

LOAD DATA LOCAL INFILE '/home/query/yldata_cz/cif_corp_inf_wd.csv'
INTO TABLE cif_corp_inf_wd
FIELDS TERMINATED BY ',' 
-- 忽略 "
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
-- 忽略第一行
IGNORE 1 LINES;

导出

SELECT
	*
FROM
	t_contract 
WHERE
	prd_code = 'aaa' 
-- 导出文件路径
INTO OUTFILE '/sunyard/mysql/t_contract.dat' 
-- 导出分隔符
FIELDS TERMINATED BY '';   

mysqldump

mysqldump -h 127.0.0.1 -uroot -p123456 pls table_name > table_name.sql