问题:MySQL AVG() 的 varchar 可能吗?

我正在使用 MySQL 在我的大学做一个数据库课程的作业。我们已经建立了一个高尔夫俱乐部的数据库,我们(除其他外)可以在其中存储来自不同游戏的玩家结果。

现在我正在为作业编写报告,并试图证明使用 int 而不是 varchar 来存储结果更好。如果使用 int,则被取消资格的玩家将获得 NULL 值作为结果。如果使用了 varchar,他们会得到一个字符串:“disqualified”。

我想知道的是:

1. 如果在计算即将发生时(例如)使用avg(),MySQL是否会自动将varchar转换为int?

2. 如果是这样,这是否会大大降低数据库的速度(与使用 int 相比)?

3. 是否可以对字符串值进行计算?例如。如果结果属性是包含“52”、“68”、“72”的varchar,可以计算平均值吗?

4. 如果我得到上面列出的字符串加上“不合格”的结果会怎样?它会像忽略一个int的NULL一样忽略那个字符串吗?

解答

您的问题可以通过运行简单的测试来回答:

drop table if exists golf;
create table golf(id int, int_col int, char_col varchar(50));
insert into golf(id, int_col, char_col) values
    (1, 10, '10'),
    (2, 20, '20');

select avg(int_col), avg(char_col) from golf;

结果:

avg(int_col) | avg(char_col)
     15,0000 |            15

http://rextester.com/NNAZ9432

如您所见, VARCHAR 列上的 AVG 返回预期结果。

现在添加一行NULL和'disqualified'

drop table if exists golf;
create table golf(id int, int_col int, char_col varchar(50));
insert into golf(id, int_col, char_col)values
    (1, 10, '10'),
    (2, 20, '20'),
    (2, NULL, 'disqualified');

select avg(int_col), avg(char_col) from golf;

现在结果不同了:

avg(int_col) | avg(char_col)
     15,0000 |            10

http://rextester.com/RXOQAZ69820

reoson 是:当NULL被 AVG 忽略时,'disqualified'被转换为0,结果为 (10 + 20 + 0) / 3 u003d 10。

要测试性能,您可以使用虚拟数据创建一个大表。在带有序列插件的 MariaDB 中,这可以很容易地完成:

drop table if exists golf;
create table golf(id mediumint primary key, int_col smallint, char_col varchar(50));
insert into golf(id, int_col, char_col)
    select seq id
         , floor(rand(1)*1000) int_col
         , floor(rand(1)*1000) char_col
    from seq_1_to_1000000;

INT 上的 AVG:

select avg(int_col) from golf;
-- query time: 187 msec

VARCHAR 上的 AVG:

select avg(char_col) from golf;
-- query time: 203 msec

最后但同样重要的是:您不应该将字符串类型用于数值。另一个原因是排序。如果您尝试对存储为字符串的数字进行排序,您将得到类似 [10, 2, 22, 3] 的结果。

您也不应该将一列用于不同的信息类型。在您的情况下,您可以再定义一列,例如status,其值为“已完成”或“不合格”。另一种可能的方法是使用值0或1的标志列disqualified。