欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mysql 数据库操作

程序员文章站 2022-06-11 09:58:16
...

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

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` = '';