这几天写存储过程，有很多次需要先将大致符合条件的数据查出来，放到临时表中。再根临时表中的数据进行更新操作。
 
期间用到了IN和EXISTS，想知道这两种匹配操作，哪个效率更高一些。
 
下面两张图是用自己机器上的数据进行的测试：
 
数据库类型：MySQL
 
使用IN的时候，
 

 
 
使用EXISTS
 

 
 
两个放一块，
 

 
 
一目了然~了然师兄
 

 
 
注意哈，临时表的CUST_ID我加了索引，如果不加索引，会相当慢，慢到我连等都不想等了。就是说在不加索引的时候，我还没见过他执行完。。。
 
创建表的时候可以选择引擎，不知道叫存储引擎还是数据库引擎还是数据库表引擎。
 
反正如果存储在设置为存储在内存的话会更快。当时数据到30w的时候，提示内存不够用了。
 
上网查了一下，可以修改MAX_HEAP_TABLE_SIZE，MAX_HEAP_TABLE_SIZE默认是16M，修改：
 
SET MAX_HEAP_TABLE_SIZE = 1024 * 1024 * 100;      -- 这样内存就会调整为100M了
 
这个值好像是在mysql的配置文件中设置的，如果在在SQL代码块中修改了，只有在重启MySQL后才会重置为原来配置的参数。
 
所以，如果临时调整这个值，最好在用完之后手动重置为原来的值。
 

 
 

 
 
好了，就是这些。
 
PS：写SQL的时候用大写感觉很吊！

在access中，update语句不能有子查询的，如不允许如下语句出现：
 
 
 
UPDATE tblA SET lngID=
      (SELECT lngID FROM tblB WHERE strName='nihao')
 
这样就需要另想办法解决问题。
 
 
 
解决方案：
 
A。
 
可以用update另外的语法解决：
 
UPDATE Tab1 a,(Select ID,Name From Tab2) b 
SET a.Name = b.Name 
WHERE a.ID = b.ID;
 
 
 
另外，像统计类的子查询还有一个方法，就是用到access特有的统计函数dsum，它的语法如下：
 
DSum 函数
 
DSum 函数可用于计算一组指定记录（域）中的一组值的总和。可以在 Visual Basic、宏、查询表达式或计算控件 中使用 DSum 函数。
 
例如，可以在查询的计算字段表达式中通过 DSum 函数来计算某一段时间内特定雇员的总销售量。或者在计算控件中使用 DSum 函数来显示特定产品的流水销售总额。
 
DSum(expr, domain, [criteria])
 
DSum 函数包含以下参数：
 
 
 
 
参数
说明
expr
表达式，用于标识被计算值的总和的数字字段。它可以是标识表或查询中的字段的字符串表达式，或者是对该字段中的数据进行计算的表达式。在 expr 中，可以包含表中的字段、窗体上的控件、常量或者函数的名称。如果 expr 包含函数，那么该函数可以是内置的或用户定义的，但不能是其他域聚合函数或 SQL 聚合函数。
domain
字符串表达式，用于标识组成域的一组记录。它可以是不需要参数的查询的表名或查询名。
criteria
可选的字符串表达式，用于约束对其执行 DSum 函数的数据的范围。例如，criteria 常常等价于 SQL 表达式中除去 WHERE 关键字的 WHERE 子句。如果 criteria 被忽略，DSum 函数将对整个域计算 expr。任何包含在 criteria 中的字段必须也是 domain 中的字段；否则 DSum 函数将返回 Null 值。
 
 
 
 
说明
 
如果没有记录满足 criteria 参数或者域中不包含记录，那么 DSum 函数会返回 Null 值。
 
不论是否在宏、模块、查询表达式或计算控件中使用 DSum 函数，都必须仔细构造 criteria 参数，以确保它将被正确计算。
 
可以使用 DSum 函数来指定这些情况下的条件：在查询的条件行中，在查询表达式内的计算字段中，或者在更新查询的“更新到”行中。
 
注释  可以将 DSum 和 Sum 函数用于总计查询中的计算字段表达式。如果使用 DSum 函数，将在数据分组之前计算值。如果使用 Sum 函数，则先进行数据分组，然后再计算字段表达式中的值。
 
需要显示不在窗体或报表的记录源中的字段的一组值的总和时，可以使用 DSum 函数。例如，假定有一个显示特定产品信息的窗体。可以在计算控件中使用 DSum 函数来随时统计产品的流水销售总额。
 

 如果需要在报表上某个控件中维持流水总和，假定所基于的字段包含在报表的记录源中，则可以使用该控件的 
 RunningSum 属性。通过 
 DSum 函数来维持窗体的流水总和。
 
 
 
注释  使用这些函数时，对 domain 内的记录的未保存更改不包括在内。如果希望使 DSum 函数基于更改的值，必须先通过单击“记录”菜单上的“保存记录”来保存更改，然后将焦点移动到其他记录，或者使用 Update 方法。
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
示例：dsum('字段a','表A',"条件B")

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。
 
 
 
例如：
 
 
 
 
 
建表语句：
 
CREATE TABLE `consume_locus` (
  `rds` varchar(255) NOT NULL,
  `topic` varchar(255) default NULL,
  `createTime` datetime default NULL,
  `dtsTime` datetime default NULL,
  PRIMARY KEY  (`rds`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 sql语句：
 
 insert into t_tableName(topic,createTime,dtsTime,rds ) values( null,'2017-02-08 14:02:50','','' )  on duplicate key update topic = values(topic),createTime = values(createTime),dtsTime = values(dtsTime),rds = values(rds) ;
 

  
 
这可以保证主键只有一条记录。
 
附上一段java代码，算是工具类。根据传入BEAN生成SQL语句。
 
package dbTest;

import java.util.Map;

public class DbBean {
	//列名和列值
	private Map<String, String> fieldMap;
	//表名
	private String tablename;
	public Map<String, String> getFieldMap() {
		return fieldMap;
	}
	public void setFieldMap(Map<String, String> fieldMap) {
		this.fieldMap = fieldMap;
	}
	public String getTablename() {
		return tablename;
	}
	public void setTablename(String tablename) {
		this.tablename = tablename;
	}
	
}
 

  
 
public class testDB {
	
	public static void main(String args[]) throws InterruptedException{
		String createTimeStr=DateUtil.dateToStr(new Date(), DateUtil.YYYYMMDDHHMMSS2);
		Map<String, String> fieldMap=new HashMap<String, String>();
		fieldMap.put("rds", ClusterClientCore.rdsCode);
		fieldMap.put("topic", ClusterClientCore.topic);
		fieldMap.put("createTime", createTimeStr);
		fieldMap.put("dtsTime", ClusterClientCore.dtsTimestamp);
		DbBean bean =new DbBean();
		bean.setFieldMap(fieldMap);
		bean.setTablename("t_tableName");
		String sql=getInsertSqlStr(bean);
			System.out.println(sql);
	}
	
	/**
	 * 把bean转换为sql语句
	 * @param bean
	 * @return
	 */
	public static String getInsertSqlStr(DbBean bean){
		StringBuffer columName=new StringBuffer();
		StringBuffer columValue=new StringBuffer();
		//幂等操作
		StringBuffer duplicateSb=new StringBuffer();
		Map<String, String> fieldMap=bean.getFieldMap();
		Iterator it=fieldMap.entrySet().iterator();
		while(it.hasNext()){
			Map.Entry<String, Object> entry=(Map.Entry<String, Object>)it.next();
			String key=entry.getKey();
			duplicateSb.append(key).append(" = values(").append(key).append("),");
			columName.append(key).append(",");
			if(entry.getValue()!=null){
			String value=(String)entry.getValue();
			columValue.append("'").append(value).append("'").append(",");
			}else{
				columValue.append(entry.getValue()).append(",");
			}
		}
		String columNameStr=columName.deleteCharAt(columName.length()-1).toString();
		String columValueStr=columValue.deleteCharAt(columValue.length()-1).toString();
		String sqlStr="insert into "+ bean.getTablename()+"("+columNameStr+" ) values( "+columValueStr+" ) ";
		String duplicateStr=" on duplicate key update "+duplicateSb.deleteCharAt(duplicateSb.length()-1).toString()+" ;";
		return sqlStr+duplicateStr;
	}
}
 

  
 
 

比如：
UPDATE test.tb_vobile a 
set a.name = '111 '
WHERE
a.id = (select max(id) id from test.tb_vobile) 

报错：
[SQL]UPDATE test.tb_vobile a 
set a.name = '111 '
WHERE
a.id = (select max(id) id from test.tb_vobile) 


[Err] 1093 - You can't specify target table 'a' for update in FROM clause



以下可通过：
UPDATE test.tb_vobile a 
join 
(select max(id) id from test.tb_vobile) b 
on a.id = b.id 
set a.name = '123 ';
或
UPDATE test.tb_vobile a ,(select max(id) id from test.tb_vobile) b 
set a.name = '321 '
WHERE
a.id = b.id ;

说明：
1、update 时，更新的表不能在set和where中用于子查询；
2、update 时，可以对多个表进行更新（sqlserver不行）；
如：update ta a,tb b set a.Bid=b.id ,b.Aid=a.id;  
3、update 后面可以做任意的查询，这个作用等同于from；