MYSQL记录
1、多表联合删除,最好不要使用别名免得出幺蛾子,貌似MySql5.0及以上版本才可以用别名(未验证过)
DELETE es_expenditure_reimbursement_record,
es_expenditure_reimbursement_all
FROM es_expenditure_reimbursement_record
LEFT JOIN es_expenditure_reimbursement_all ON es_expenditure_reimbursement_record.er_id = es_expenditure_reimbursement_all.er_id
WHERE es_expenditure_reimbursement_record.batch_id = #{batchId,jdbcType=BIGINT}
AND es_expenditure_reimbursement_record.org_id = #{orgId,jdbcType=BIGINT}
---------------------持续更新20190307
2、名称首字母搜索
BEGIN
DECLARE V_RETURN VARCHAR(200);
SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(P_NAME USING GBK),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
RETURN V_RETURN;
END
-
CONVERT:对象转换字符集
- CONVERT(expr USING transcoding_name)
- 例子:SELECT CONVERT('abc' USING utf8);
- CONVERT(string, CHAR[(N)] CHARACTER SET charset_name)
- 例子:SELECT CONVERT('test', CHAR CHARACTER SET utf8);
- CAST(string AS CHAR[(N)] CHARACTER SET charset_name)
- 例子:SELECT CAST('test' AS CHAR CHARACTER SET utf8);
-
LEFT:截取从左到右指定长度
- LEFT(str,length)
- 例子:SELECT LEFT('MySQL LEFT', 5);// MySQL
-
HEX:将一个字符串或数字转换为十六进制格式的字符串
- 例子:SELECT HEX('10');// 3130
-
UNHEX:把十六进制格式的字符串转化为原来的格式
- 例子:SELECT UNHEX('3130');// 10
- 例子:SELECT UNHEX('3130');// 10
-
CONV:转换进制
- CONV(N,from_base,to_base):N是要转换的数据,from_base是原进制,to_base是目标进制
- 例子:SELECT CONV(16,10,16)// 10
-
INTERVAL:获取第一个参数在后续列表的位置,[a,b)
- INTERVAL(N,N1,N2,N3,...):进行比较列表(N,N1,N2,N3等等)中的N值
- 如果N<N1返回0,如果N<N2返回1,如果N<N3返回2 等等。如果N为NULL,它将返回-1。列表值必须是N1<N2<N3的形式才能正常工作
- 例子:SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10);// 6
-
ELT:
-
例子:SELECT ELT(3,'hello','halo','test','world');// test
-
---------------------持续更新20190509