MySQL-实验中遇到的问题

吐槽一下：老师就光给了所谓的实验指导，里面甚至还有错误，一切都得靠自己去摸索，去谷歌，去求助同学，真的是费时又费力。

为什么不能给一个详细的操作步骤呢，有时候在网上真的找不到解决问题的方法，会因为一个报错卡住好长时间，热情逐渐被磨灭。

所以老师的作用到底体现在了哪里？

---

一、数据导入遇到的一系列问题

1.要先建立没有外键的表

例如在本实验中，要先建立没有箭头指向的表，也就是REGION表和PART表，它们不用参考其他的表。

-- 地区表
create table REGION(
    R_REGIONKEY int primary key,
    R_NAME char(25),
    R_COMMENT varchar(152)
);

-- 零件表
create table PART(
    P_PARTKEY int primary key,
    P_NAME varchar(55),
    P_MFGR char(25),
    P_BRAND char(10),
    P_TYPE varchar(25),
    P_SIZE int,
    P_CONTAINER char(10),
    P_RETAILPRICE real,
    P_COMMENT varchar(23)
);
  
-- 国家表
create table NATION(
    N_NATIONKEY int primary key,
    N_NAME char(25),
    N_REGIONKEY int,
    N_COMMENT varchar(152),
    foreign key(N_REGIONKEY) references REGION(R_REGIONKEY)
);

-- 供应商表
create table SUPPLIER(
    S_SUPPKEY int primary key,
    S_NAME char(25),
    S_ADDRESS varchar(40),
    S_NATIONKEY int,
    S_PHONE char(15),
    S_ACCTBAL real,
    S_COMMENT varchar(101),
    foreign key(S_NATIONKEY) references NATION(N_NATIONKEY)
);

-- 客户表
create table CUSTOMER(
    C_CUSTKEY int primary key,
    C_NAME varchar(25),
    C_ADDRESS varchar(40),
    C_NATIONKEY int,
    C_PHONE char(15),
    C_ACCTBAL real,
    C_MKTSEGMENT char(10),
    C_COMMENT varchar(117),
    foreign key(C_NATIONKEY) references NATION(N_NATIONKEY)
);

-- 供应商-零件表
create table PARTSUPP(
    PS_PARTKEY int,
    PS_SUPPKEY int,
    PS_AVAILQTY int,
    PS_SUPPLYCOST real,
    PS_COMMENT varchar(199),
    primary key(PS_PARTKEY,PS_SUPPKEY),
    foreign key(PS_PARTKEY) references PART(P_PARTKEY),
    foreign key(PS_SUPPKEY) references SUPPLIER(S_SUPPKEY)
);

-- 订单表
create table ORDERS(
    O_ORDERKEY int primary key,
    O_CUSTKEY int,
    O_ORDERSTATUS char(1),
    O_TOTALPRICE real,
    O_ORDERDATE date,
    O_ORDERPRIORITY char(15),
    O_CLERK char(15),
    O_SHIPPRIORITY int,
    O_COMMENT varchar(79),
    foreign key(O_CUSTKEY) references CUSTOMER(C_CUSTKEY)
);

-- 订单明细表
create table LINEITEM(
    L_ORDERKEY int,
    L_PARTKEY int,
    L_SUPPKEY int,
    L_LINENUMBER int,
    L_QUANTITY decimal,
    L_EXTENDEDPRICE real,
    L_DISCOUNT real,
    L_TAX real,
    L_RETURNFLAG char(1),
    L_LINESTATUS char(1),
    L_SHIPDATE date,
    L_COMMITDATE date,
    L_RECEIPTDATE date,
    L_SHIPINSTRUCT char(25),
    L_SHIPMODE char(10),
    L_COMMENT varchar(44),
    primary key(L_ORDERKEY, L_LINENUMBER),
    foreign key(L_ORDERKEY) references ORDERS(O_ORDERKEY),
    foreign key(L_PARTKEY) references PART(P_PARTKEY),
    foreign key(L_SUPPKEY) references SUPPLIER(S_SUPPKEY)
);

2.建完表后需要调整表结构，以便之后导入数据。

本实验中要部分导入的数据和表结构不符合，需要调整。

但是老师给出的sql命令语句是错误的，需要自己重新写。

-- 调整表结构
alter table PART modify column P_TYPE varchar(25) after P_MFGR;
alter table PART modify column P_RETAILPRICE real after P_TYPE;
alter table CUSTOMER modify column C_NATIONKEY int after C_NAME;
alter table ORDERS modify column O_ORDERDATE date after O_CUSTKEY;

3.将CSV文件导入数据库

1.将CSV文件的编码格式改为UTF-8

mysql默认的编码格式UTF-8,所以得把要导入文件的编码格式都改为UTF-8。

操作：将csv文件的后缀改为txt，用记事本打开，选择另存为,在右下方将编码格式改为UTF-8，再将后缀改回csv，保存。

2.导入数据前的准备工作

1.查看是否允许从外部路径导入文件

-- 输入指令
show variables like '%secure%';

-- 结果
mysql> show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         |       | -- 这里要为空才可以，表示可以从任意的外部路径向数据库中导入数据
+--------------------------+-------+
2 rows in set, 1 warning (0.01 sec)

最开始会出现secure_file_priv=‘系统给的默认路径’，表示系统只能从默认路径中导入数据。

但是我后来将csv文件移动到该目录下，依然无法导入，可能还存在些问题。

修改方式：复制系统给的默认路径，在我的电脑里找到该路径。

或者在我的电脑里输入C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

能够看到一个my.ini配置文件，右键属性，点击安全，编辑。选中Users，勾选完全控制，点击应用，确定。

之后才有权限修改my.ini文件。

打开my.ini文件，搜索[mysqld]，找到secure-file-priv=‘系统给的默认路径’，将引号里的内容全部删掉，空格也不要有。

即secure-file-priv=''

关键一步：重启mysql，并不是exit退出再登录。而是在计算机管理中，点击服务，找到mysql，右键重新启动。

然后再重新登录mysql。

2.查看是否允许本地导入

-- 输入指令
show variables like 'local_infile';

-- 结果
mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    | -- 这里要为on
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

-- 修改为on
set global local_infile=on;

最开始local_infile=off，需要执行指令修改为on。

3.导入数据

一般来说已经可以导入数据了，但是我执行导入数据的语句后依然会报错，要么就是没有权限导入，要么就是找不到文件。

解决方法：从命令行中进入mysql，同时设置local-infile变量为1

win + R进入指令框，输入cmd打开命令行。但是我在登录mysql的时候，会报错'mysql' 不是内部或外部命令，也不是可运行的程序

因为我一直在使用mysql的客户端，似乎没有配置过环境变量。

---

命令行中的mysql实际上是一个可执行程序。

出现报错的原因是，系统找不到mysql.exe这个程序。

解决办法：给系统一个目录，让系统可以找到。

把mysql.exe所在的目录，添加到环境变量中即可。

复制该路径，搜索高级系统设置

点击环境变量

在用户变量找到Path，点击编辑，将复制的路径添加到里面。重启cmd命令行，即可运行mysql。

-- 通常登录mysql的指令
C:\Users\86176>mysql -u root -p
Enter password: -- 输入密码即可

-- 导入数据时要执行的指令
C:\Users\86176>mysql -h localhost -u root --local-infile=1 -p
Enter password: -- 输入密码即可

现在就彻底地完成了所有导入数据前的准备工作。可以进行数据导入了。

-- 导入CSV数据
load data local infile 'C:\\Users\\86176\\Desktop\\database\\lineitem.csv' # 文件路径
into table LINEITEM           # 表名
character set utf8                 # 编码
fields terminated by ','           # 分隔符
lines terminated by '\r\n';        # 换行符，windows下是\r\n

第一行的文件路径要把\都改为\\

注意：导入数据的顺序和建表的顺序要一致，即先导入没有外键的表的数据，否则会出现空表。

二、mysql登录不上 ERROR 1045 (28000)

在登录mysql的时候，突然登不上了。

使用mysql客户端输入密码后会直接闪退，使用命令行输入mysql -u root -p，在输入密码后会出现以下报错。

ERROR 1045 (28000): Access denied for user ‘user’@‘localhost’ (using password: YES)

折腾半天后，我认为是系统可能把我的登录密码给抹除了，所以需要强行地修改密码。

在CSDN找到一篇文章完美的解决了我的问题。

https://blog.csdn.net/m0_46308522/article/details/128851046?spm=1001.2014.3001.5506

以下修改方式适用于mysql8.0版本：

Step1.停止 MySQL 服务

需要在计算机管理中点击服务，找到mysql，点击停止服务。

Step2.创建一个名为 mysql-init.txt 的文本文件，里面有一行内容

ALTER USER 'root'@'localhost' IDENTIFIED BY '你想设置的密码';，然后保存

说明：

• 后续会调用这个文件，以设置为 ‘你想设置的密码’。
• 这个文件可以放在任意路径下，为便于后续的步骤，我直接放在 C 盘根目录下了

Step3.

1.以管理员身份运行 cmd

2.切换到 MySQL 8.0 程序所在的 bin 目录，可以输入该指令：cd C:\Program Files\MySQL\MySQL Server 8.0\bin

3.输入 mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --init-file=C:\\mysql-init.txt

我尝试的时候直接复制粘贴即可，但可能不适用于所有人。

具体的输入指令：

4.输入完上述命令后，回车。发现光标停留在空行上，这说明修改成功，如下图所示：

5.关闭当前命令行，必须关闭，否则无法启动 MySQL 服务

6.删除 mysql-init.txt 文件

7.在计算机管理—服务中启动mysql，然后再次登录（使用新设置的密码），发现可以登录成功