mysql 插入/更新数据

时间：2022-09-21 17:11:20

mysql 插入/更新数据

 

INSERT 语句

1、一次性列出全部字段的值，例如：

INSERT INTO student VALUES('Chenqi','M', 29);
INSERT INTO student VALUES('Bush','M', 60),('Obama', 'M', 45);

允许同时插入多个数据行；

 

2、只对部分字段赋值

INSERT INTO student(name, sex) VALUES ('Abby', 'F'),('Joseph', 'M');

没有在INSERT语句中出现的字段将被赋予默认值。允许同时插入多个数据行；

 

3、使用SET子句

INSERT INTO student SET name='Stein', sex='M';

没有在SET子句里出现的字段被赋予默认值。这种形式的ISNERT语句不允许一次插入多个数据行。

 

REPLACE 语句

REPLACE语句和INSERT语句用法非常相似，唯一的区别就是，在一个UNIQUE索引或PRIMARY KEY中出现重复值的时候，REPLACE会先删除旧行后再插入新行，INSERT语句则报错。
如果数据表没有使用PRIMARY KEY或UNIQUE索引，则REPLACE与INSERT语句完全一样。

 

INSERT ... ON DUPLICATE KEY UPDATE Syntax

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。 

 

例如，如果列 a 为拥有UNIQUE索引，并且包含值1，

CREATE TABLE t (id INT AUTO_INCREMENT,a INT, b INT, c INT, PRIMARY KEY(id), UNIQUE KEY(a));

INSERT INTO t (a,b,c) VALUES (1,9,11);

则以下两个语句具有相同的效果：

INSERT INTO t (a, c) VALUES (1, 3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;

如果行作为新记录被插入，则受影响行（affected-rows）的值显示1；如果原有的记录被更新，则受影响行的值显示2。 

mysql 插入/更新数据

 

 

也可以INSERT多行记录：

INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;

执行后，c 的值会变为 13 (c 在原值上+1).

 

mysql 插入/更新数据

 

 

使用VALUES函数：

INSERT INTO  t(a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c)+1;

执行后，c 的值会变为 8 (c 在插入值上+1).

mysql 插入/更新数据

 

 

 

LOAD DATA LOCAL INFILE file_name INTO TABLE table_name

默认情况下，LOAD DATA语句假设各字段的值以'\t'分隔，数据行以'\n'分隔，数据值的排列顺序与字段在数据表里的先后顺序一致。

LOCAL关键字可以使客户程序读取数据文件并发送到服务器以加载，如果省略LOCAL关键字，就表示数据文件是保存在服务器主机上的，而你必须拥有相应的FILE服务器访问权限才能把文件里的数据加载到数据表里去。

 

加载数据文件的另一种方法是使用mysqlimport工具，例如：

mysqlimport -r -L -u username -p passwd -h hostname -P port db_name file_name

该命令将生成一条把file_name文件里的数据值加载到db_name的指定数据表里去的LOAD DATA语句。

注意：mysqlimport 程序是根据数据文件的名字来确定与之对应的数据表的，例如：

mysqlimport -L sampdb member.txt

就是将member.txt数据文件加载到sampdb.member数据表的。

 

mysqlimport 的选项：

-r：--replace，对于unique key上的重复记录，使用新行取代旧记录；

-i：--ignore，对于unique key上的重复记录，忽略不处理；

-L：--local，从客户端本地主句读取输入文件；

-c，--columns，指定输入数据文件中的字段列表（有序）；
-D，--delete，在导入新数据文件之前，清空数据表；

 

 

 

有效加载数据

MySQL对于大规模数据加载（mass insertion）的场景，通常考虑以下几个方面：

1、批量加载的效率比逐行加载的效率高，因此要尽量使用INSERT INTO语句一次插入多个数据行的功能；

2、使用LOAD DATA语句要比使用INSERT语句效率高，服务器只需要对一个语句进行语法分析和解释，并且索引只在所有数据行都处理完成后才需要刷新；

3、使用LOAD DATA语句要比使用LOAD DATA LOCAL语句效率高，因为前者服务器可以直接从磁盘上读取文件，后者则需要通过网络将数据文件送到服务器上。

 

如果只能使用多个INSERT语句，要尽可能地对它们分组以减少索引的刷新次数，可以通过事务而不用自动提交的方式来实现这一点：

START TRANSACTION
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
COMMIT;

 

对于非事务性的存储引擎，可以通过对数据表加写锁定的方式来实现：

LOCK TABLES tbl_name WRITE;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
INSERT INTO tbl_name ... ;
UNLOCK TABLES;

 

对于MyISAM数据表，减少索引刷新次数的另一个策略是使用DELAY_KEY_WRITE数据表选项。

 

 

 

 

参考文档：

http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

 

标签：

相关文章