MySQL使用

目录

• MySQL使用
  • 服务安装
  • 无密码登录
  • 服务控制
    • 常用命令
      • 安装类
      • 结构
        • 生成数据库
        • 导出数据库结构
        • 执行脚本
        • 删除外键约束
      • 查询
        • 查看用户
        • 查看所有外键
        • 查询大表
        • 查询数据库记录数
      • 函数
        • 时间戳
      • 管理
        • 查看最大连接配置
        • 显示全部连接
        • 修改最大连接
        • 用户
          • 添加用户
        • 权限
          • 其它机器可访问
          • 赋予数据库访问权限
          • 赋予所有的所有
    • 常见问题 - {:.} ERROR 1819 (HY000): Your password does not satisfy the current policy require
      • 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated
      • 修改blob字段长度

MySQL使用

服务安装

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

无密码登录

服务控制

sudo systemctl start mysql.service   
sudo systemctl stop mysql.service
sudo systemctl restart mysql.service
sudo systemctl status mysql.service

常用命令

安装类

apt install mysql-client

mysql -u root -p
mysql -h110.110.110.110 -u root -p

结构

生成数据库

CREATE DATABASE IF NOT EXISTS contractdb_dev  DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

show databases;

use databasename;
show tables;

describe tablename;

导出数据库结构

mysqldump -h192.168.0.98 -uroot -pjustkey.net -d contractdb_dev > contractdb_dev.sql

执行脚本

source xxx.sql

删除外键约束

show index from accounts;

alter table accounts drop foreign key accounts_institution_id_fkey;

drop index accounts_institution_id_fkey on accounts;

查询

查看用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

查看所有外键

select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA ='fish_chain_test1' and CONSTRAINT_NAME like '%fkey';

查询大表

select table_name,table_rows from  tables order by table_rows desc limit 10;

查询数据库记录数

SELECT 
    SUM(TABLE_ROWS)
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'fish_chain_test_jiajc'

函数

时间戳

unix_timestamp(now())

管理

查看最大连接配置

show variables like '%max_connections%';

显示全部连接

show status like 'Threads%';
show full processlist;

修改最大连接

临时修改

set GLOBAL max_connections=256;

配置文件修改(my.cnf)

max_connections=256;

用户

添加用户

create user 'editest'@'localhost' identified by 'editest123456';
create user 'editest'@'%' identified by 'editest123456';
flush privileges;

权限

其它机器可访问

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'qwe123' WITH GRANT OPTION;
FLUSH PRIVILEGES

赋予数据库访问权限

grant all on order_test.*  to test;

赋予所有的所有

grant all on *.* to 'test'@'%';

常见问题

ERROR 1819 (HY000): Your password does not satisfy the current policy require

参考： https://www.cnblogs.com/ivictor/p/5142809.html https://blog.csdn.net/weixin_44198965/article/details/91870959

英文翻译：错误1819（hy000）：您的密码不符合当前策略要求 validate_password_policy取值：

Policy	Tests Performed
0 or LOW	Length
1 or MEDIUM	Length; numeric, lowercase/uppercase, and special characters
2 or STRONG	Length; numeric, lowercase/uppercase, and special characters; dictionary file

默认是1，即MEDIUM，所以刚开始第一次设置的密码必须符合该参数的长度，并且必须含有数字、小写大写字母、特殊字符。

设置的密码为：@wjb13191835106，很明显我错误的原因就是密码太过于简单，不符合mysql密码策略

set global validate_password_policy=0;
set global validate_password_length=1;
alter user 'root'@'localhost' identified by '@wjb13191835106';

1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated

sql8中缺省的sql_mode的问题

set sql_mode = select replace( @@sql_mode,'ONLY_FULL_GROUP_BY,','')

修改blob字段长度

-- 查看表结构
desc tms_case_flow;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| case_id      | bigint     | NO   | PRI | NULL    |       |
| flow_content | blob       | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+
-- 新建临时表
create table case_flow_temp like  tms_case_flow;

-- 修改字段长度
alter table case_flow_temp add column flow_content blob(320000);

-- 复制数据
insert into case_flow_temp select * from tms_case_flow;

-- 删除旧表
drop table tms_case_flow;

-- 重命名新表
alter table case_flow_temp rename to tms_case_flow;

-- 查看表结构
desc tms_case_flow;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| case_id      | bigint     | NO   | PRI | NULL    |       |
| flow_content | mediumblob | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+

--  blob->mediumblob