SELECT`column`FROM`table`WHERE`field`like'%keyword%';
SELECT`column`FROM`table`WHERE`field`like'keyword%';
1.LOCATE('substr',str,pos)方法
SELECT`column`FROM`table`WHERELOCATE('keyword', `field`)>0
2.POSITION('substr' IN `field`)方法
SELECT`column`FROM`table`WHEREPOSITION('keyword'IN`filed`)
3.INSTR(`str`,'substr')方法
SELECT`column`FROM`table`WHEREINSTR(`field`,'keyword')>0
UPDATE tbl_ser_apply a set order_no_desc = REVERSE (SUBSTRING(a.order_no, -6))
ALTER TABLE `tbl_ser_apply` ADD INDEX order_no_desc ( `order_no_desc` )
select a.*,a1.id as id2,a1.order_no as orderNo2,a1.tran_amt as tranAmt2,a1.fee_amt as feeAmt2,a1.repayment_date_req as repaymentDateReq2
,a1.status as status2,a1.create_time as createTime2,a1.update_time as updateTime2
from (
select tsa.id,tsa.order_no as orderNo,tsa.repayment_date_req as repaymentDateReq,tsa.`status`,tsa.fee_state as feeState,tsa.repayment_flag as repaymentFlag,
tsa.capital_return_flag as capitalReturnFlag,tsa.tran_amt as tranAmt,tsa.fee_amt as feeAmt,tsa.capital_returned_amont as capitalReturnedAmont,
tsa.wait_amt as waitAmt,tsa.back_charge_amt as backChargeAmt,tsa.create_time as createTime,tui.real_name as realName,tui.mobile_no as mobileNo,
tc.bank_card_no as bankCardNo,tmi.merchant_name as merchantName,tui.mer_no as merNo,tsa.reserved1 as reserved1,tsa.parent_id as parentId,tc.bank_name as bankName,tc.holder_name as holderName,
tc.certificate_no as certificateNo
from tbl_ser_apply as tsa LEFT JOIN tbl_user_info as tui on tsa.userid=tui.id LEFT JOIN tbl_merchant_inf as tmi on
tmi.merchant_no=tui.mer_no LEFT JOIN tbl_cusinfo tc on tc.id=tsa.cusInf_id where tsa.order_no_desc like REVERSE('%372191')
ORDER BY tsa.create_time desc ) a LEFT JOIN tbl_ser_apply a1 on a.parentId=a1.id
alter table tbl_ser_apply add column virtual_col varchar(20) as (REVERSE (SUBSTRING(tbl_ser_apply.order_no, -6)));
ALTER TABLE `tbl_ser_apply` ADD INDEX virtual_col ( ` virtual_col` )
[ GENERATED ALWAYS ] AS ( ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT ]
select a.*,a1.id as id2,a1.order_no as orderNo2,a1.tran_amt as tranAmt2,a1.fee_amt as feeAmt2,a1.repayment_date_req as repaymentDateReq2
,a1.status as status2,a1.create_time as createTime2,a1.update_time as updateTime2
from (
select tsa.id,tsa.order_no as orderNo,tsa.repayment_date_req as repaymentDateReq,tsa.`status`,tsa.fee_state as feeState,tsa.repayment_flag as repaymentFlag,
tsa.capital_return_flag as capitalReturnFlag,tsa.tran_amt as tranAmt,tsa.fee_amt as feeAmt,tsa.capital_returned_amont as capitalReturnedAmont,
tsa.wait_amt as waitAmt,tsa.back_charge_amt as backChargeAmt,tsa.create_time as createTime,tui.real_name as realName,tui.mobile_no as mobileNo,
tc.bank_card_no as bankCardNo,tmi.merchant_name as merchantName,tui.mer_no as merNo,tsa.reserved1 as reserved1,tsa.parent_id as parentId,tc.bank_name as bankName,tc.holder_name as holderName,
tc.certificate_no as certificateNo
from tbl_ser_apply as tsa LEFT JOIN tbl_user_info as tui on tsa.userid=tui.id LEFT JOIN tbl_merchant_inf as tmi on
tmi.merchant_no=tui.mer_no LEFT JOIN tbl_cusinfo tc on tc.id=tsa.cusInf_id where tsa.virtual_col like '372191%'
ORDER BY tsa.create_time desc ) a LEFT JOIN tbl_ser_apply a1 on a.parentId=a1.id
联系客服