Your Site Title

MySql 介绍

目录结构

	/var/lib/mysql - 数据目录
	/var/lib/mysql-files - 临时数据目录
	/var/run/mysqld - socket 目录
	/etc/mysql/my.cnf - 配置文件

重新初始化(Failed to initialize DD Storage Engine. )

	mysqld -remove MySQL
	sudo mysqld --initialize-insecure

重置密码

1. 默认root 无密码 8.0

    1. vim /etc/mysql/my.cnf (/etc/my.cnf)
    2. 添加, 无密码登录
            [mysqld]
            skip-grant-tables
    3. 重置密码
            UPDATE mysql.user SET authentication_string=null WHERE User='root';
            UPDATE mysql.user set authentication_string=PASSWORD("rootpwd) WHERE User="root" and Host="localhost";
            FLUSH PRIVILEGES;
            exit;
    4. 修改密码
            ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';
            ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123';
   

字符

• mysql使用utf8mb4经验吐血总结

The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. The utf8mb4 character set uses a maximum of four bytes per character supports supplementary characters:

• For a BMP character, utf8 and utf8mb4 have identical storage characteristics: same code values, same encoding, same length.

• For a supplementary character, utf8 cannot store the character at all, whereas utf8mb4 requires four bytes to store it. Because utf8 cannot store the character at all, you have no supplementary characters in utf8 columns and need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.

  SHOW VARIABLES LIKE ‘character%’; show variables like ’%collation%’; // 库的字符集 Select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME,SQL_PATH from information_schema.SCHEMATA; // 表的字符集 show table status from test like ‘%t6%’/G; // 列的字符集 show full columns from test.books;

  // 所有字符集 SHOW CHARACTER SET; // 所有字符串的排序规则 (字段大小写是否敏感) SHOW COLLATION;

命令

Y表示有权限 ，N表示无权限
# 1.mysql.user表 (all)
select * from mysql.user where user='root';

# 2.mysql.db表 (empty)
select * from mysql.db where user='root';

# 3.mysql.tables_priv (empty)
select * from mysql.tables_priv where user='root';

# 4.mysql.colums_priv表 (empty)
select * from mysql.columns_priv where user='root';

# 5.mysql.procs_priv (empty)
select * from mysql.procs_priv where user='root';

mysql -h 主机名 -u 用户名 -p

show databases;
CREATE DATABASE <name>;
use <name>;

show tables;
create table tablename(columns)

CREATE USER <user_account> IDENTIFIED BY <password>;
SHOW GRANTS FOR <dbadmin>@<localhost>;
CREATE USER superadmin@'%' IDENTIFIED BY 'mypassword';
CREATE USER zhengwu@'%' IDENTIFIED BY 'Zhengwu#456';

GRANT SELECT , INSERT ON testdb.news TO 'shunping'@'localhost'
GRANT ALL ON testdb.* TO 'shunping'@'%';

drop user <user>@<localhost>;

select user();
select user,host from mysql.user;
show grants for 'pdh'@'%';


// 添加自增id
alter table user modify id integer auto_increment ;        

// 查看库下表的数量
SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = (SELECT database()) ORDER BY TABLE_ROWS DESC;

SELECT COUNT(*) AS table_count FROM information_schema.tables WHERE table_schema = 'HBZWTEST';

自增主键

SELECT LAST_INSERT_ID() AS id;

CREATE TABLE tb_student2 (
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> PRIMARY KEY(ID)
-> )AUTO_INCREMENT=100;

ALTER TABLE TB_GVRP_HEAD MODIFY SID BIGINT PRIMARY KEY FIRST;

FIRST 不是规范sql, 指定当前字段为第一列, (数据库实现有可能不是第一列)

ALTER TABLE TB_BS_CUSTOMER MODIFY SID BIGINT NOT NULL AUTO_INCREMENT;

修改AUTO_INCREMENT值
ALTER TABLE tableName AUTO_INCREMENT = n

数据处理

清空表数据

truncate table 表名
DELETE FROM TB_HBCOM_BUSINESS_INFO WHERE 1=1;

problem

1. ONLY_FULL_GROUP_BY

5.7 默认 ONLY_FULL_GROUP_BY
SELECT list is not in GROUP BY clause and contains nonaggregated column ‘×××’

show variables like '%sql_mode';

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

2. You do not have the SUPER privilege and binary logging is enabled

mysql -u root -p

mysql>set global log_bin_trust_function_creators = 1;

永久解决方案如下：
修改配置文件，在[mysqld]部分加上：
log_bin_trust_function_creators=1
注：linux系统配置文件/etc/my.cnf
windows系统配置文件my.ini

Reference

Installing MySQL Shell on Linux

• MySQL 教程

This site is open source. Improve this page.