Mysql 数据库操作

把一个表中数据更新到另一个表：

UPDATE 
   cflow c, order o
SET 
   c.order_no = o.order_no
WHERE
   a.`name` = o.`name`

表中某个字段为varchar存储的json格式，若想获取json中某个属性对应的值

SELECT REPLACE(REPLACE(UNHEX(SUBSTRING(HEX(remark),1,LENGTH(SUBSTRING_INDEX(HEX(remark),HEX(','),1)))),'"HandleFee":"',''),'"','') AS HandleFee, created_at, app_type, order_no, trade_money, platform_type
FROM (
	SELECT UNHEX(SUBSTRING(HEX(remark),LENGTH(SUBSTRING_INDEX(HEX(remark),HEX('TranAmount'),1))-1,LENGTH(HEX(remark)))) AS remark, created_at, app_type, order_no, trade_money, platform_type
	FROM `admin_bank_handle_record`
	WHERE handle_type = 1 AND record_status = 3) AS a;

 

将数据库表更新时间字段设置为自动更新：

ALTER TABLE `my_db`.`test_record` 
MODIFY COLUMN `updated_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间' AFTER `created_at`;

 

SELECT DISTINCT CONCAT('update notice_record set bank_code = ','''',CASE WHEN a.bank_name LIKE '中信银行%' THEN 'B012' WHEN a.bank_name LIKE '中国邮政储蓄银行%' THEN 'B011' ELSE '' END,'''',' where id = ','''',b.id,''';')
FROM `authentication_record` AS a INNER JOIN notice_record AS b
ON a.bank_number = b.`bank_no`
AND b.`bank_code` = '';