on duplicate key update 怎么用 请举个例子说明 如题 on duplicate key update 是什么意思? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 为什么不看一下手册中的说明呢?MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html mysql> create table test1(id int,name varchar(5),type int,primary key(id));Query OK, 0 rows affected (0.01 sec)mysql> create table test2(id int,name varchar(5),type int,primary key(id));Query OK, 0 rows affected (0.01 sec)mysql> select * from test1;+-----+------+------+| id | name | type |+-----+------+------+| 101 | aaa | 1 | | 102 | bbb | 2 | | 103 | ccc | 3 | +-----+------+------+3 rows in set (0.00 sec)mysql> select * from test2;+-----+------+------+| id | name | type |+-----+------+------+| 201 | aaa | 1 | | 202 | bbb | 2 | | 203 | ccc | 3 | | 101 | xxx | 5 | +-----+------+------+4 rows in set (0.00 sec)发现重复的是更新操作。在原有记录基础上,更新指定字段内容,其它字段内容保留。例如我只想插入test2表的id,name字段,但是要保留test1表的type字段:mysql> insert into test1(id,name,type)(select id,name,type from test2) on DUPLICATE KEY UPDATE test1.name=test2.name;Query OK, 5 rows affected (0.04 sec)Records: 4 Duplicates: 1 Warnings: 0mysql> select * from test1;+-----+------+------+| id | name | type |+-----+------+------+| 101 | xxx | 1 | | 102 | bbb | 2 | | 103 | ccc | 3 | | 203 | ccc | 3 | | 202 | bbb | 2 | | 201 | aaa | 1 | +-----+------+------+6 rows in set (0.00 sec) 求教百万级数据库的效率问题 急急急!如何在mqsql里面创建一个函数? mysql-gui-tools-noinstall-5.0-r4-win32.zip mysql还原数据备份时是如何防止冲突的? mysql状态监控软件 postgres历史记录占满了硬空间清理问题 表中有一项字段值输入错了,请问怎样修改? 如何修复超过1G的MYSQL 数据表 mysql的临时表问题~~100 分区与分表 mysql 5.1 以上版本在哪里下载 Mysql 执行时间
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
Query OK, 0 rows affected (0.01 sec)mysql> create table test2(id int,name varchar(5),type int,primary key(id));
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test1;
+-----+------+------+
| id | name | type |
+-----+------+------+
| 101 | aaa | 1 |
| 102 | bbb | 2 |
| 103 | ccc | 3 |
+-----+------+------+
3 rows in set (0.00 sec)mysql> select * from test2;
+-----+------+------+
| id | name | type |
+-----+------+------+
| 201 | aaa | 1 |
| 202 | bbb | 2 |
| 203 | ccc | 3 |
| 101 | xxx | 5 |
+-----+------+------+
4 rows in set (0.00 sec)发现重复的是更新操作。在原有记录基础上,更新指定字段内容,其它字段内容保留。例如我只想插入test2表的id,name字段,但是要保留test1表的type字段:
mysql> insert into test1(id,name,type)(select id,name,type from test2) on DUPLICATE KEY UPDATE test1.name=test2.name;
Query OK, 5 rows affected (0.04 sec)
Records: 4 Duplicates: 1 Warnings: 0mysql> select * from test1;
+-----+------+------+
| id | name | type |
+-----+------+------+
| 101 | xxx | 1 |
| 102 | bbb | 2 |
| 103 | ccc | 3 |
| 203 | ccc | 3 |
| 202 | bbb | 2 |
| 201 | aaa | 1 |
+-----+------+------+
6 rows in set (0.00 sec)