安装 Mysql 数据库

下载安装包

安装地址：https://dev.mysql.com/downloads/mysql/
下载安装包，解压到磁盘，随便哪个磁盘都可以。

配置环境变量

新建-> 变量名：MYSQL_HOME，变量值：D:\mysql-8.0.18-winx64

配置MSql 配置文件

在 D:\mysql-8.0.18-winx64这个目录下新建一个my.ini文件，写入如下配置信息：

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\\mysql-8.0.18-winx64
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=D:\\mysql-8.0.18-winx64\\data
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

生成data文件

以管理员的身份打开cmd，进到这个目录下 D:\mysql-8.0.18-winx64\bin，执行以下命令
mysqld --initialize-insecure --user=mysql

安装mysql

继续执行以下命令
mysqld -install
显示 Service successfully installed.

启动服务

net start mysql

显示服务没有启动起来，打开服务看一下，按住window标志+r，输入services.msc

找到mysql，启动一下：

如果启动失败，找到本地安装了其他的php集成环境，先将这些服务停止：

在启动即可成功。

再重新命令行启动 net start mysql，可以看到服务启动成功了。

image.png

登录mysql

初始mysql的登录名是root，密码是空，运行命令mysql -u root -p，直接回车，

可以看到进去到mysql的命令行界面了。
现在开始设置用户名和密码，由于我下载的是新版本，所以新版本的语法变了，不能使用以前的方式，新语法如下 ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';，将root用户的密码设置成123456，返回成功。mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)

退出mysql

quit

再重新登录mysql

mysql -u root -p
回车输入密码123456，

登录成功。

查看当前的状态

输入status

mysql> status
--------------
mysql  Ver 8.0.18 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id:          18
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter:        ;
Server version:         8.0.18 MySQL Community Server - GPL
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 2 hours 31 min 20 sec

Threads: 2  Questions: 20  Slow queries: 0  Opens: 125  Flush tables: 3  Open ta
bles: 45  Queries per second avg: 0.002
--------------

可以看到当前的用户是root@localhost 端口是3306 当前的数据库为空。

简单的sql操作

显示所有数据库

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.34 sec)

创建数据库 使用数据库

create database koa_test;
use koa_test;

mysql> create database koa_test;
Query OK, 1 row affected (0.37 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| koa_test           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use koa_test
Database changed
mysql> status;
--------------
mysql  Ver 8.0.18 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id:          21
Current database:       koa_test
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter:        ;
Server version:         8.0.18 MySQL Community Server - GPL
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 20 hours 9 min 56 sec

Threads: 2  Questions: 30  Slow queries: 0  Opens: 143  Flush tables: 3  Open ta
bles: 63  Queries per second avg: 0.000

mysql> create table domain(id int NOT NULL auto_increment, name char null, statu
s int null, PRIMARY KEY (id));
Query OK, 0 rows affected (2.97 sec)
//创建表，查询表
mysql> SELECT * FROM domain;
Empty set (0.37 sec)
--------------

插入数据，查询数据

mysql> INSERT INTO `domain` (id, name, status) VALUES (null, 'n', 402);
Query OK, 1 row affected (1.44 sec)

mysql> INSERT INTO `domain` (id, name, status) VALUES (null, 'a', 400);
Query OK, 1 row affected (0.33 sec)

mysql> SELECT * FROM domain;
+----+------+--------+
| id | name | status |
+----+------+--------+
|  1 | n    |    402 |
|  2 | a    |    400 |
+----+------+--------+
2 rows in set (0.02 sec)

mysql> SELECT status from domain where status=400;
+--------+
| status |
+--------+
|    400 |
+--------+
1 row in set (0.10 sec)

Node 连接操作数据库

先处理执行 Node.js 代码连接报错问题

Node 在链接数据库的时候报错： Client does not support authentication protoc ol requested by server; consider upgrading MySQL client。

出现上述问题的原因是：mysql8 之前的版本中加密规则是 mysql_native_password,而在 mysql8 之后,加密规则是 caching_sha2_password，把 mysql 用户登录密码加密规则还原成 mysql_native_password。
修改步骤如下：
1.查看MYSQL数据库中所有用户

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',''';') AS query FROM mysql.user;
+------------------------------+
| query                        |
+------------------------------+
| User: 'mysql.infoschema'@''; |
| User: 'mysql.session'@'';    |
| User: 'mysql.sys'@'';        |
| User: 'root'@'';             |
+------------------------------+
4 rows in set (2.51 sec)

2.修改加密规则，因为最新版的加密规则好像不一样，安装的过程当中是有提示的：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NE
VER;
Query OK, 0 rows affected (2.51 sec)

3..修改用户的认证规则:

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '1
23456';
Query OK, 0 rows affected (0.23 sec)

4.刷新权限：

mysql> flush privileges;
Query OK, 0 rows affected (0.28 sec)

5.重启mysql服务：

D:\mysql-8.0.18-winx64\bin>net stop mysql
MySQL 服务正在停止............
MySQL 服务已成功停止。

D:\mysql-8.0.18-winx64\bin>net start mysql
请求的服务已经启动。

再执行Node.js 连接数据库，并查询数据

新建一个项目koa-test

npm init -f
npm i mysql -S

新建server.js用来连接数据库

//server.js
const mysql = require('mysql');

const conn = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'koa_test'
})

conn.connect((err) => {
    if(err){
        throw err;
    }
    console.log('mysql 连接成功');
});

//插入数据
conn.query('insert into domain(name, status) values ("a", "500");', (error, result, fileds) => {
    if(error){
        throw error;
    }
    console.log('插入数据', result, '\n');
});

//删除数据
conn.query('delete from domain where id=4;',  (error, result, fileds) => {
    if(error){
        throw error;
    }
    console.log('删除数据', result, '\n');
});
//查询数据
conn.query('select * from domain;', (error, result, fileds) => {
    if(error){
        throw error;
    }
    let data = [];
    result.forEach(item => {
        data.push({
            id: item.id,
            name: item.name,
            status: item.status
        })
    });
    console.log('查询数据', data, '\n');
});
//关闭连接
conn.end();

执行 node server.js，可以看到数据库链接成功，查询语句的结果也返回了：

$ node mysql/server.js
mysql 连接成功
插入数据 OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 8,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

删除数据 OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

查询数据 [ { id: 1, name: 'n', status: 402 },
  { id: 2, name: 'a', status: 400 },
  { id: 3, name: 'a', status: 500 },
  { id: 5, name: 'a', status: 500 },
  { id: 6, name: 'a', status: 500 },
  { id: 7, name: 'a', status: 500 },
  { id: 8, name: 'a', status: 500 } ]

看下数据库返回的结果：

D:\mysql-8.0.18-winx64\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| koa_test           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.12 sec)

mysql> use koa_test;
Database changed
mysql> select * from domain;
+----+------+--------+
| id | name | status |
+----+------+--------+
|  1 | n    |    402 |
|  2 | a    |    400 |
|  3 | a    |    500 |
|  5 | a    |    500 |
|  6 | a    |    500 |
|  7 | a    |    500 |
|  8 | a    |    500 |
+----+------+--------+
7 rows in set (0.00 sec)

参考：https://blog.csdn.net/qq_38455201/article/details/83024357