MySQL数学运算与文本处理

本文介绍了MySQL数据库中基本的数学运算，文本查询条件，以及文本数据的常用操作，如判断文本占用的字节数和包含的字符数量、合并、截取、查找文本位置等，最后会演示如何从18位身份证号码里提取性别数据。

数学运算

数据处理过程中需要大量的计算，计算过程中经常会使用扩展字段或临时字段保存计算结果，下面介绍一些常用的数学运算。

基本的算术运算包括加（+）、减（-）、乘（*）、除（/）。在计算过程中，如果涉及小数部分，则需要根据数据的精度要求合理设用小数位数，如金额计算的最终结果常见为2位小数，可以在保存数据和计算过程增加1到2位的冗余，提高数据计算和保存的精度。显示计算结果时，可以使用format()函数指定小数位数，格式为format(<数据>, <小数位>)，数据中多出位数的数据会四舍五入。

另一个可以设置保留小数位数的函数是round()，其格式为round(<数据>,<小数位>)。其中，<小数位>默认为0，此时只保留整数部分，最多可指定保留30位小数。<小数位>还可以使用负数，如指定为-1时可以保留整十的数据，设置为-2时可以保留整百的数据，以此类推。使用round()函数时，多出位数的数据同样会四舍五入。请注意，如果两个参数中有一个为null，则函数返回null。与format()不同的是，round()函数返回为数值类型，而format()函数返回的是字符串类型。

floor()函数，返回小于或等于参数数据的最大整数。如果参数为null则返回null。

ceil()和ceiling()函数，返回大于或等于参数数据的最小整数。如果参数为null则返回null。

求余数。MySQL中求余数可以使用%运算符，也可以使用mod()函数；求余数运算时可以使用包含小数部分，如10.1%3.3结果为0.2。

幂运算。使用pow(x,y)或power(x,y)函数，函数会计算x的y次方，如果x或y参数为null则函数返回null。如pow(2,3)返回8。使用幂运算函数还可以进行开方运算，此时的y参数格式为“1/n”，n为大于0的整数，此时计算x开n次方；计算开方时需要注意结果的小数位精度问题，如“select round(pow(8,1/3),1)”会返回2，如果不使用round()函数指定保留1位小数，则显示结果为1.9999999986137056（64位浮点数）；实际应用中可以根据实际情况指定保留的小数倍数。

计算算术平方根时使用sqrt()函数。如sqrt(4)返回2、sqrt(3)返回1.7320508075688772。计算结果可以根据需要保留小数位，如上学时使用的算术平方根表中就使用了3位小数。参数为null时返回null。

计算绝对值时使用abs()函数，函数会返回参数的绝对值，参数为null时返回null。

此外，MySQL中还内置了一系列的三角函数，如sin()、cos()、tan()等。

文本查询条件

MySQL数据库中的文本（字符串）内容应定义在一对英文单引号（'）中，查询条件中使用文本数据时，数据会根据编码进行匹配，同样可以使用比较运算，如等于（=）、不等于（<>或!=）、大于（>）、大于等于（>=）、小于（<）、小于等于（<=）；也可以使用in、between...and等类型的条件。

如果需要对文本数据进行模糊查询，可以使用字符通配符或正则表达式，下面介绍字符通配符的使用，下一篇文章将讨论正则表达式在MySQL中的应用。

对文本类型进行模糊查询时使用like运算符，条件设置的基本格式为：

<字段名> like '<查询文本>'

在<查询文本>中，可以使用下画线（_）表示一个字符，使用百分号（%）表示零个或多个字符。接下来的示例使用t2表的数据进行演示，其原始数据如下图所示。

t2表原始数据

下面的代码，我们查询姓氏为“李”的记录。

use cdb_demo;

select * from t2 where f2 like '李%';

本例条件将“李”字作为查询条件的起始部分，然后有多少字符都可以，查询结果如下图所示。

查询结果

下面的代码会查询姓氏为“刘”，并且全名有三个字的记录。

use cdb_demo;

select * from t2 where f2 like '刘__';

请注意，在条件中“刘”字后是2个下画线，用于匹配两个字符，即匹配以“刘”开始的3个字符。查询结果如下图所示。

查询结果

下面的代码会查询姓名中包含“文”字的记录，无论它在什么位置。

use cdb_demo;

select * from t2 where f2 like '%文%';

本例，在匹配的文本内容中，“文”字前后各使用了一个%通配符，查询结果如下图所示。

查询结果

查询具体的内容时可以直接使用相等运算，如下面的代码会查询“A组”的记录。

use cdb_demo;

select * from t2 where f3='A组';

查询结果如下图所示。

查询结果

使用比较条件、bwtween...and条件查询文本数据时应注意，如果文本编码是有序的，并且与数据实际含义相同，则数据的查询才会有意义。如下面的代码会查询G0010到G0015的记录。

use cdb_demo;

select * from t2 where f1 between 'G0010' and 'G0015';

查询结果如下图所示。

查询结果

文本的字节数和字符数据

MySQL中，length()函数可以获取文本数据占用的字节数量，而char_length()函数可以获取文本中的字符数量。下面的代码演示了这两个函数的应用。

use cdb_demo;

set @s = '中文测试ABCD';
select length(@s); -- 16
select char_length(@s); -- 8

代码中定义了@s变量，并赋值为'中文测试ABCD'，然后分别获取@s变量数据的字节数和字符数量。本合集示例数据库使用了utf8mb4编码，每个字符占用2个字节，所以length()函数会返回16；而char_length()函数则返回字符数量8。可以分别执行select语句进行测试。

文本合并

concat()函数可以将多个参数合并，函数会返回合并后的字符串；如果有一个参数是null则结果返回null。下面的代码会根据t2表中f4的数据分别显示姓名和称呼（先生或女士）。

use cdb_demo;

select concat(f2,if(f4=1,'先生',if(f4=2,'女士',''))) from t2;

本例使用了if()函数，其功能是当参数1的值为“真”时返回参数2的数据，否则返回参数3的数据。代码的功能是，当f4为1时显示“姓名+先生”，否则当f4为2时显示“姓名+女士”，如果f4不是1也不是2则称呼显示为空白字符串。如果能够确认f4字段数据不是1就是2，也可以简化条件，如下面的代码。

use cdb_demo;

select concat(f2,if(f4=1,'先生','女士')) from t2;

本代码，当f4为1时显示“先生”，否则显示“女士”。

文本截取

截取文本内容可以使用substring()、mid()、left()、right()等函数，下面分别了解。

substring()和mid()函数功能相同，其参数设置为(str,pos,len)，其中，str为原始文本，pos指定开始截取字符的位置，len指定截取的字符数量，如果不指定则返回pos位置以后的所有内容。下面的代码演示了mid()函数的应用。

set @s = '中文测试ABCD';
select mid(@s,1,2); -- 中文
select mid(@s,5); -- ABCD
select mid(@s,5,3); -- ABC

代码中使用mid()截取@s变量的文本内容，大家可以逐一测试这些select语句。

left(str,len)函数可以截取str开始部分的len个字符；right(str,len)函数则截取str结束部分的len个字符，下面的代码演示了这两个函数的应用。

set @s = '中文测试ABCD';
select left(@s,4); -- 中文测试
select right(@s,4); -- ABCD
select right(@s,3); -- BCD

substring_index(str,delim,count)函数的功能是从str中截取使用delim分割的count个部分；默认会从前往后截取，如果count设置为负数则从后往前截取。下面的代码演示了substring_index()函数的应用。

set @s = 'a.b.c.d';
select substring_index(@s, '.', 3); -- a.b.c
select substring_index(@s, '.', -3); -- b.c.d

本例，@s变量包含了a.b.c.d，字母之间使用圆点分隔，substring_index()函数中，指定使用圆点作为分割符，第一个select语句显示前3个，即a.b.c；第二次调用显示后3个，即b.c.d。

查找文本位置

locate(substr,str,pos)函数的功能是在str中查找substr，pos指定开始查找的位置，默认从第一个字符开始查找。找到内容后，函数返回substr第一次出现的位置，没有找到返回0。下面的代码演示了locate()函数的应用。

set @s = '中文测试ABCD中文';
select locate("中文",@s); -- 1
select locate("BCD",@s); -- 6
select locate("中文",@s,5); -- 9
select locate("中文测试",@s,5); -- 0

提取身份证号码中的性别

在示例的t1表中，f3字段保存了虚拟的18位身份证号码，添加的f7字段用于保存性别数据，如果没有f7字段，可以参考如下代码添加。

use cdb_demo;

alter table t1 add column f7 tinyint not null default 0 check(f7 in (0,1,2));

下面的代码会根据f3字段中第17位数字进行判断，如果是奇数则f7更新为1，如果是偶数则f7更新为2，即1表示男，2表示女。

use cdb_demo;

update t1 set f7= if(mid(f3,17,1)%2=1, 1, 2);

代码中，在获取性别数据时首先使用mid()函数截取身份证中第17位字符，然后计算它除以2的余数，当余数为1时返回1，否则返回2。请注意，本例使用了无条件的update语句，执行操作会更新所有记录的数据。