当前位置：首页 > article >正文

MySQL(导入sql文件)

article 2025/2/1 6:52:02

传文件省略…(从windows传到linux)

改编码格式

在这里插入图片描述
为什么不在windows里面修改呢？因为windows打开发现根本打不开直接就卡住了数据过多了（4百万数据(不信可以自己试一下)）

[root@CentOS8 ~]# file order_info.sql
order_info.sql: UTF-8 Unicode text

iconv -f utf8 -t gb2312 order_info.sql -o order_info1.sql
-f 是文件原编码 
-t是文件要转换编码
-o是输出
[root@CentOS8 ~]# file order_info1.sql
order_info1.sql: ISO-8859 text
#这里根据自己需求修改。

#查看当前操作系统编码和语言
[root@CentOS8 ~]# echo $LANG
en_US.UTF-8
#如果想修改
LANG=zh_CN.UTF-8

#为了避免出错都改成utf8就行 utf8mb4是正宗的utf8字符集，使用1～4个字节表示字符。
mysql> show variables like 'character_set_client';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_client | utf8mb4 |
+----------------------+---------+
1 row in set (0.00 sec)
 
mysql> SHOW VARIABLES LIKE 'character_set_connection';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_connection | utf8mb4 |
+--------------------------+---------+
1 row in set (0.01 sec)
 
mysql> SHOW VARIABLES LIKE 'character_set_results';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set (0.00 sec)

#可以把三个参数（character_set_client、character_set_connection、character_set_results）设置成一样。
SET NAMES utf8mb4;

改文件格式

yum -y install dos2unix   #dos(windows) -> unix(linux)

[root@CentOS8 ~]# file order_info.sql
order_info.sql: UTF-8 Unicode text, with CRLF line terminators
You have new mail in /var/spool/mail/root

[root@CentOS8 ~]# dos2unix order_info.sql
dos2unix: converting file order_info.sql to Unix format...
[root@CentOS8 ~]# file order_info.sql
order_info.sql: UTF-8 Unicode text

导入

mysql> source /root/order_info.sql

检查

mysql> select count(*) from order_info;
+----------+
| count(*) |
+----------+
|  1036109 |
+----------+
1 row in set (0.28 sec)

假如表的编码格式是lat1(意外情况)

mysql> show create table DICT_REGION_CITY;
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                  |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DICT_REGION_CITY | CREATE TABLE `DICT_REGION_CITY` (
  `code` varchar(36) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `parent_code` varchar(36) DEFAULT NULL,
  `LEVEL_CODE` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table DICT_REGION_CITY default character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table DICT_REGION_CITY change code code varchar(36) character set utf8;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table DICT_REGION_CITY change name name varchar(36) character set utf8;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table DICT_REGION_CITY change parent_code parent_code varchar(36) character set utf8;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table DICT_REGION_CITY change name name varchar(100) character set utf8;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table DICT_REGION_CITY change LEVEL_CODE LEVEL_CODE varchar(100) character set utf8;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table DICT_REGION_CITY;
+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                     |
+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DICT_REGION_CITY | CREATE TABLE `DICT_REGION_CITY` (
  `code` varchar(36) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `parent_code` varchar(36) DEFAULT NULL,
  `LEVEL_CODE` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)