创建数据库

CREATE DATABASE DATABASENAME;

创建用户

CREATE USER 'taikang'@'localhost' IDENTIFIED BY 'taikang';

设置max_allowed_packe

set global max_allowed_packet=1024*1024*50;

查询max_allowed_packet

show global variables like 'max_allowed_packet';

查询表中某一字段重复数据

select param_key,count(*) as count from conf_business_param  group by param_key having count>1;

查询表中重复记录

SELECT
    result.param_key 
FROM
    ( SELECT param_key, count(*) AS count FROM conf_business_param GROUP BY param_key HAVING count > 1 ) AS result
    LEFT JOIN conf_business_param ON conf_business_param.param_key = result.param_key

导入视图函数

set global log_bin_trust_function_creators=TRUE;

修改mysql验证模式

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'p@ssw0rd';

创建索引

ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);
-- 或者
ALTER TABLE projectfile ADD INDEX (fileuploadercode, projectid);

替换字段中重复的内容

update table set name = replace(name,'name_','') where name like 'name_%';--替换
update table set name = substr(name,6) where name like 'name_%'; --截取

mysql中的substr()函数

用法：

substr(string string,num start,num length);

string为字符串；

start为起始位置；

length为长度。

mysql中的start是从1开始的

substr(name,6) 从第六位开始到最后一位