# 客户端连接服务器时,加上参数 --local-infile
mysql --local-infile -u root -p
set global local_infile = 1;
load data local infile '文件路径'
into table tb_user
fields terminated by ','
lines terminated by '\n';
创建一个数据库,并建立需要导入数据的表结构。
create database test;
use test;
CREATE TABLE tb_user(
id INT NOT NULL AUTO_INCREMENT
, username VARCHAR(50) NOT NULL
, password VARCHAR(50) NOT NULL
, name VARCHAR(20) NOT NULL
, birthday DATE DEFAULT NULL
, sex CHAR(2) DEFAULT NULL
, PRIMARY KEY (id)
, UNIQUE KEY unique_user_username(username)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- sql脚本
load data infile 'C:/ProgramData/MySQL/tb_user.sql'
into table tb_user
fields terminated by ','
lines terminated by '\n';
-- csv文件
load data infile 'C:/ProgramData/MySQL/tb_user.csv'
into table tb_user
fields terminated by ','
lines terminated by '\r\n';
如果出现 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement ,找到my.ini文件(一般在C:\ProgramData\MySQL\MySQL Server 8.0 目录下)打开,在文末添加 secure_file_priv='' 保存,然后停止mysql再启动mysql((二)MySQL的安装、启动/停止/连接、卸载)
--------------------------窗口A-------------------------------
use world;
begin;
select * from city;
-- 执行update语句, 此时id=1的行锁定
update city set name = 'new_name_1' where id = 1;
再在窗口B同时开启一个事务:
--------------------------窗口B-------------------------------
use world;
begin;
select * from city;
-- 执行update语句,id=4可以执行成功
update city set name = 'new_name_2' where id = 4;
--------------------------窗口A-------------------------------
use world;
begin;
select * from city;
-- 执行update语句, name没有索引,则上的是表锁
update city set name = 'new_name_1' where name = 'Kabul';
--------------------------窗口B-------------------------------
use world;
begin;
select * from city;
-- 执行update语句, 不能执行成功,因为表被锁定了
update city set name = 'new_name_2' where name = 'Mazar-e-Sharif';