CHAR_LENGTH(str)
返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含五个二字节字符集, LENGTH()返回值为 10,而CHAR_LENGTH()的返回值为5。
CHARACTER_LENGTH(str)
CHARACTER_LENGTH()是CHAR_LENGTH()的同义词。
BIT_LENGTH(str)
返回2进制长度.
简单的总结来说,mysql中获取字符串长度的有两个函数:
length:返回字符串所占的字节数,是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符
char_length:返回字符串所占的字符数,不管汉字还是数字或者是字母都算是一个字符
有时候写mapper的时候用到map类型的传参,写法如下
// 获取方法查询入参
public Map<String, Object> getCommonParams(CaseRecordsInfoModel caseRecordsInfoModel, Pageable pageable) {
Map<String, Object> params = new HashMap<String, Object>();
if (caseRecordsInfoModel.getRecord() != null) {
if (StringUtils.isNotBlank(caseRecordsInfoModel.getRecord().getUserId())) {
params.put("userId", caseRecordsInfoModel.getRecord().getUserId());
}
if (pageable != null) {
params.put("offset", pageable.getOffset());
params.put("pageSize", pageable.getPageSize());
}
return params;
}
service 层
@Override
public int selectPageByParamsCount(Map<String, Object> params, Pageable pageable) {
return caseRecordsInfoRepo.selectPageByParamsCount(params);
}
mapper层
<select id="selectPageByParams" resultMap="BaseResultMap" parameterType="Map">
select
<include refid="Base_Column_Lists" />
from case_records_info a , case_records record where record.if_out=0
<if test="_parameter.containsKey('userId')" >
and record.user_id not in
<foreach item="item" index="index" collection="items"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="_parameter.containsKey('ifAboveM7') and ifAboveM7 =='false'.toString()" >
and record.collection_type in
<foreach item="item" index="index" collection="types"
open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="_parameter.containsKey('ifAboveM7') and ifAboveM7=='true'.toString()" >
and (record.collection_type > 'M7' or LENGTH(record.collection_type)>2 or record.collection_type in
<foreach item="item" index="index" collection="types"
open="(" separator="," close=")">
#{item}
</foreach>)
</if>
<if test="_parameter.containsKey('sendAreaName') and ifNoAdr=='true'.toString()">
and a.send_area_name = ''
</if>
<if test="_parameter.containsKey('sendAreaName') and ifNoAdr=='false'.toString()">
and a.send_area_name like CONCAT(#{sendAreaName},'%' )
</if>
and a.case_id=record.id and a.is_deleted=0 and
record.is_deleted=0 ORDER BY record.user_id ASC
<if test="_parameter.containsKey('offset')">
limit #{offset},#{pageSize}
</if>
</select>
mapper 还有多条件判断
比如<if test="_parameter.containsKey('sendAreaName') and ifNoAdr=='false'.toString()">
这里就是判断某个变量字符串是不是“true”再进行sql语句的编写。
总体mapper 使用map参数的用法就讲到这。