MySQL AVG() 的 varchar 可能吗？

Question

我正在大学的数据库类(class)中使用 MySQL 做作业。我们建立了一个高尔夫俱乐部的数据库，我们(除其他外)可以在其中存储不同比赛的玩家成绩。

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

我想知道的是:

1. MySQL 是否自动转换 varchar至int如果avg() (例如)当计算即将发生时使用？
2. 如果是的话，数据库速度是否会大幅下降(与使用 int 相比)？
3. 是否可以对字符串值进行计算？例如。如果结果属性是 varchar包含“52”、“68”、“72”，可以计算平均值吗？
4. 如果我得到上面列出的字符串加上“不合格”的结果，会发生什么情况？它会像忽略 int 的 NULL 一样忽略该字符串吗？

Answer 1

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

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

原因是:当AVG忽略NULL时，'disqualified'被转换为0，结果为( 10 + 20 + 0)/3 = 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;

平均得分与平均得分的比值:

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

AVG 优于 VARCHAR:

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

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

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