mysql关联修改SQL及long与datetime类型相互转换

1.关联修改

#解决思路  UPDATE tb1,tb2 SET tb1.address=tb2.address WHERE tb1.name=tb2.name
UPDATE car c,tmpcolorcode t SET c.color_code=t.color_code WHERE c.reg_no=t.reg_no;

2.long与datetime类型相互转换

#时间datetime转换为long格式
SELECT UNIX_TIMESTAMP(NOW());  #1410403824

#时间long转换为datetime格式
SELECT FROM_UNIXTIME(1410403824);  #2014-09-11 10:50:24

3.在实际的处理过程中，需要除以1000

SELECT id,mobile,CODE,FROM_UNIXTIME(create_time/1000) AS createTime,TYPE FROM `mobile_code` ORDER BY id DESC; 

 4.Java代码：

public static void main(String[] args) {
        Date d = new Date();
        System.out.println("long型数据: " + d.getTime());
        System.out.println("sys型数据: " + System.currentTimeMillis());
    }

long型数据: 1410404639799
sys型数据: 1410404639799

SELECT FROM_UNIXTIME(1410404639799/1000);

 

转载于:https://www.cnblogs.com/simpledev/p/3965964.html