1.1将准备好的sql语句文件导入到数据库内
load data local infile '/Users/develop/Desktop/sql1.log' into table tb_user fields terminated by',' lines terminated by '\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
1.2 解决报错问题
1.2.1 开启local_infile
mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.04 sec)
mysql> set global local_infile=1;
Query OK, 0 rows affected (0.01 sec)
mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)
1.2.2 如果发现还是不可以
使用以下命令登录Mysql:
mysql --local-infile -uroot -p
mysql> desc tb_user;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(45) | NO | UNI | NULL | |
| password | varchar(96) | NO | | NULL | |
| name | varchar(45) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| email | varchar(45) | YES | | NULL | |
| phone | varchar(45) | YES | | NULL | |
| qq | varchar(32) | YES | | NULL | |
| status | varchar(32) | NO | | NULL | |
| create_time | datetime | NO | | NULL | |
| update_time | datetime | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
可以看到username 存在索引,name 不存在索引,我们拿user_name跟name字段举例
mysql> select username,name from tb_user where name='"name12345"';
+-----------------+-------------+
| username | name |
+-----------------+-------------+
| "username12345" | "name12345" |
+-----------------+-------------+
1 row in set (0.26 sec)
mysql> select username,name from tb_user where id='12345';
+-----------------+-------------+
| username | name |
+-----------------+-------------+
| "username12345" | "name12345" |
+-----------------+-------------+
1 row in set (0.00 sec)
可以看出,使用索引确实会加快查询速度
内容来源于网络,如有侵权,请联系作者删除!