Mysql问题记录

1、SELECT k.imei,p.imsi,n.userid, m.report_type,m.report_time,k.result_code FROM MC_MESSAGE_INFO t,MC_MESSAGE_HIS_1221 k, MC_PHONE_INFO p, MC_MESSAGE_REPORT_1221 m,MC_DEVICETOKEN_MAP_p99999999 n WHERE t.infoid=k.infoid AND k.msgid=m.msgid AND k.imei=p.imei AND p.imei=n.imei AND t.requestid = ‘6766875878778’ AND t.appid LIKE '%%p999%%' AND k.imei=n.imei

     在多张表中查询数据时，最好把所有表中关联的字段关联上，例如多张表中有imei都需要关联。

     k.imei=p.imei AND p.imei=n.imei AND k.imei=n.imei  不等价于  k.imei=p.imei=n.imei，在具体项目中，后者查询的结果为某条记录重复显示。

2、SELECT t.requestid FROM mc_message_info t WHERE (t.dealed=0,t.dealed is NULL) AND (DATE_FORMAT(t.endtime,'%%Y%%m%%d%%H%%i%%S') <= '%s') union SELECT k.requestid FROM dmpush_message_info k WHERE (DATE_FORMAT(k.endtime,'%%Y%%m%%d%%H%%i%%S') <= '%s') AND (k.dealed=0 OR k.dealed is NULL);

     查找出两张表中所有不重复的某个字段。

3、INSERT INTO MC_FILE_NUM(requestid,file_num,file_name,sync_stat)  SELECT '%s',%d,'%s',0 FROM DUAL WHERE not exists(select * from MC_FILE_NUM WHERE requestid=%s AND sync_stat=1)

    向表中不存在的记录插入数据，如果存在不插入。

4、select distinct requestid from MC_FILE_NUM where 条件

    查询单个表中所有不重复的某个字段。

    此种方法无法实现查找所有不重复的单个字段：select distinct  t.requestid as requestid ,k.requestid as requestid  from mc_message_info t,    dmpush_message_info k where 条件

5、文件数据导入

文本内容类似：

A100000D9AD083|2012-12-24 00:37:22
A100000D9AD083|2012-12-24 00:37:22
A100000D9AD083|2012-12-24 00:37:52
A100000D9AD083|2012-12-24 00:38:22

创表：

CREATE TABLE `mc_heartbeat_1224` (

  `imei` varchar(32) DEFAULT NULL,

  `heartbeat_time` varchar(32) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `mc_heartbeat_1226` (

  `imei` varchar(32) DEFAULT NULL,

  `heartbeat_time` varchar(32)  DEFAULT NULL,

  `import_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行如下sql导入成功(按列导入)：LOAD DATA INFILE 'E:/dmpush/Project/mc_import_heartbeat_ace/config/heartbeat2012-12-24.1.log'  INTO TABLE mc_heartbeat_1224 FIELDS TERMINATED BY '|' (imei,heartbeat_time) 

执行如下sql导入成功(默认对应字段导入): LOAD DATA INFILE 'E:/dmpush/Project/mc_import_heartbeat_ace/config/heartbeat2012-12-24.1.log'  INTO TABLE mc_heartbeat_1224 FIELDS TERMINATED BY '|' 

执行如下sql导入失败(按列导入)：LOAD DATA INFILE 'E:/dmpush/Project/mc_import_heartbeat_ace/config/heartbeat2012-12-24.1.log'  INTO TABLE mc_heartbeat_1224(imei,heartbeat_time) FIELDS TERMINATED BY '|'

执行如下sql导入成功(按列导入，默认时间自填)：LOAD DATA INFILE 'E:/dmpush/Project/mc_import_heartbeat_ace/config/heartbeat2012-12-26.1.log'  INTO TABLE mc_heartbeat_1226 FIELDS TERMINATED BY '|'  (imei,heartbeat_time)

注：mysql这个语法有点怪