Mysql的常见错误及常用操作（根据身份证、生日获取年龄等）

以下是mysql的一些常见操作及问题处理，后续会陆续追加

【Mysql】如何查询数据库中存在某一个字段的所有表？

数据库里的表很多，一时之间忘记了要找的表，只记得其中的某些字段，可以用下面的sql来查询在这个数据库中，存在这个字段的所有表，是不是就缩小查找范围了呢？ 

SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'sex' AND TABLE_SCHEMA='dbName' AND TABLE_NAME NOT LIKE 'sex%';

mysql创建函数错误，

set global log_bin_trust_function_creators=TRUE;

mysq根据生日获取年龄:

SELECT birthday , IFNULL(TIMESTAMPDIFF(YEAR, birthday, CURDATE()),0) AS age FROM table(你的表名称)

SELECT TIMESTAMPDIFF(YEAR, birthday, CURDATE()) age ,birthday FROM table(你的表名称)

mysq根据身份证修改生日:

update

table(你的表名称) set birth_date = DATE_FORMAT(SUBSTR(id_card,7,8),'%Y-%m-%d')

where LENGTH(id_card)=18 and id_card regexp '^[1-9][[:digit:]]{7}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}$|^[1-9][[:digit:]]{5}[1-9][[:digit:]]{3}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}([0-9]|X)$'

--------------数据库表信息查看

select TABLE_SCHEMA '数据库名称',TABLE_NAME '表名称', COLUMN_NAME '字段名称',COLUMN_TYPE '字段类型',EXTRA AS 'PK主键',is_nullable '是否可为空',COLUMN_COMMENT '字段说明' from information_schema.columns where TABLE_SCHEMA='数据库名称';

SELECT table_name, table_comment

FROM information_schema.tables

WHERE table_schema = '数据库名称'

-- 修改字段属性，以0001的形式进行展示

alter table 表名称 MODIFY COLUMN 字段名称 int(4) zerofill not null

--mysql的复杂性策略设置

关闭密码复杂性策略

SET global validate_password_policy = 0;

设置密码复杂性要求密码最低长度为1

SET global validate_password_length = 1;

查看密码复杂性策略

SELECT @@validate_password_policy;

查看密码复杂性要求密码最低长度大小

SELECT @@validate_password_length;

#尝试修改密码

#自己选方式都可

alter user 'root'@'localhost' identified by '123456';

set password for 'root'@'localhost' = password('123456');

set password for 'root'@'%' = password('123456');

#刷新配置

flush privileges;

设置mysql登录失败、登录超时等

INSTALL PLUGIN connection_control SONAME 'connection_control.so';

show variables like 'connection_control%';

SHOW GLOBAL VARIABLES LIKE '%timeout%';

set GLOBAL wait_timeout=300;

set GLOBAL interactive_timeout=300;

install plugin CONNECTION_CONTROL soname 'connection_control.so';

install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.dll';

show variables like '%plugin%';

install plugin CONNECTION_CONTROL soname 'connection_control.dll';

INSTALL PLUGIN CONNECTION_CONTROL

SONAME 'connection_control.dll';

INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS

SONAME 'connection_control.dll';

mysql删除身份证重复的数据

delete ymf_person_base_info

from

ymf_person_base_info,

(

SELECT

min(id) id,

id_card

FROM

ymf_person_base_info

GROUP BY

id_card

HAVING

count(*) > 1

) t2

where ymf_person_base_info.id_card = t2.id_card

and ymf_person_base_info.id > t2.id

----mysql连接报错

Connections using insecure transport are prohibited while --require_secure_transport=ON

set global require_secure_transport=off

mysql查看数据的总数量

1.数据库查询总条数

use information_schema;

select sum(table_rows) from tables where TABLE_SCHEMA = 数据库名称;

2.mysql 查看数据库中所有表的记录数

use information_schema;

select table_name,table_rows from tables

where TABLE_SCHEMA = '数据库名称'

order by table_rows asc;