Linux中mysql导入.sql文件报mysql: ERROR 1146 (42S02): Unknown error 1146

Linux中mysql导入.sql文件报mysql: ERROR 1146 (42S02): Unknown error 1146

注意：此篇文章不适合小白（不会数据库的和Linux命令），但可以提供指导1.在导入之前不能连不上数据库

root是数据库账户名（安装数据库时的），127.0.0.1是指本地

Enter :   //是对应的数据库密码

成功后会进入如下界面：

[root@HR01 ~]# mysql -u root -p -h 127.0.0.1  
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 599
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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>

2.进入数据库创建，查看，删除数据库操作

show ;        //查询当前所有数据库

use ;   //进入相应数据库

show ;                //查看相应数据库的表

附赠创建和删除数据库的语句：

;//是自己数据库的名字

drop ; //删除数据库,自己查询和drop的区别

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| FUCK               |
| java_database      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
mysql> use java_database;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------------------+
| Tables_in_java_database       |
+-------------------------------+
| count_message                 |
| departmentannualassessment    |
| departmentquarterlyassessment |

| personalquarterlyassessments  |
| tb_expend                     |
| tb_message                    |
+-------------------------------+
6 rows in set (0.00 sec)
mysql>

3.使用语句导入.sql文件

使用命令将’/DATA/‘下的.sql文件导入数据库mysql error 1146，如果成功会如下显示：

mysql> source /DATA/java_database.sql;
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

4.mysql: ERROR 1146 (42S02):   error 1146

重点来了，本人遇到的问题是在使用 16导出的.sql文件时，一直报mysql: ERROR 1146 (42S02):   error 1146错误，经过排障，发现是字符集的问题mysql error 1146，所以设置了Linux下的mysql的字符集规则

1.在 16中对应的数据库上右键即可查看字符集和规则

2.然后在Linux中查看我们的数据库支持的字符集和规则

mysql> SHOW CHARACTER SET;
+----------+------------------+---------------------+--------+
| Charset  | Description      | Default collation   | Maxlen |
+----------+------------------+---------------------+--------+
| big5     |                  | big5_chinese_ci     |      2 |
| dec8     |                  | dec8_swedish_ci     |      1 |
| cp850    |                  | cp850_general_ci    |      1 |
| hp8      |                  | hp8_english_ci      |      1 |
| koi8r    |                  | koi8r_general_ci    |      1 |
| latin1   |                  | latin1_swedish_ci   |      1 |
| latin2   |                  | latin2_general_ci   |      1 |
| swe7     |                  | swe7_swedish_ci     |      1 |
| ascii    |                  | ascii_general_ci    |      1 |
| ujis     |                  | ujis_japanese_ci    |      3 |
| sjis     |                  | sjis_japanese_ci    |      2 |
| hebrew   |                  | hebrew_general_ci   |      1 |
| tis620   |                  | tis620_thai_ci      |      1 |
| euckr    |                  | euckr_korean_ci     |      2 |
| koi8u    |                  | koi8u_general_ci    |      1 |
| gb2312   |                  | gb2312_chinese_ci   |      2 |
| greek    |                  | greek_general_ci    |      1 |
| cp1250   |                  | cp1250_general_ci   |      1 |
| gbk      |                  | gbk_chinese_ci      |      2 |
| latin5   |                  | latin5_turkish_ci   |      1 |
| armscii8 |                  | armscii8_general_ci |      1 |
| utf8     |                  | utf8_general_ci     |      3 |
| ucs2     |                  | ucs2_general_ci     |      2 |
| cp866    |                  | cp866_general_ci    |      1 |
| keybcs2  |                  | keybcs2_general_ci  |      1 |
| macce    |                  | macce_general_ci    |      1 |
| macroman |                  | macroman_general_ci |      1 |
| cp852    |                  | cp852_general_ci    |      1 |
| latin7   |                  | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode    | utf8mb4_general_ci  |      4 |
| cp1251   |                  | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode   | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode | utf16le_general_ci  |      4 |
| cp1256   |                  | cp1256_general_ci   |      1 |
| cp1257   |                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode   | utf32_general_ci    |      4 |
| binary   |                  | binary              |      1 |
| geostd8  |                  | geostd8_general_ci  |      1 |
| cp932    |                  | cp932_japanese_ci   |      2 |
| eucjpms  |                  | eucjpms_japanese_ci |      3 |
| gb18030  |                  | gb18030_chinese_ci  |      4 |
+----------+------------------+---------------------+--------+
41 rows in set (0.00 sec)
mysql>

3.如果有支持的字符集和规则，随后就可以修改为支持的字符集了：

mysql> ALTER DATABASE java_database CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

但是设置完成后，还是报上面的问题，我就继续找，发现导入的字符集和规则正确，但是导出的正不正确呐？虽然查看过数据库的字符集合规则，但是我们用的是第三方工具Linux中mysql导入.sql文件报mysql: ERROR 1146 (42S02): Unknown error 1146，就一定能保证正确吗？所以我查看了导出的.sql文件（把.sql文件拖入到 中，就可以看到）。

果不其然，我一开始是直接将一个表中的所有字段全选中，然后在选项中修改字符集和规则mysql error 1146，但是保存后还是没修改完成，于是就一个一个点过去修改并保存Linux中mysql导入.sql文件报mysql: ERROR 1146 (42S02): Unknown error 1146，然后再在Linux中导入时就成功了。