MariaDB 虚拟列 - 我可以有哈希吗? [英] MariaDB Virtual Columns - Can I have a hash?

问题描述

MariaDB 具有虚拟列，允许您自动对其他列数据执行操作.例如，如果您想以不同的度量单位显示数据，只需除以 10 或 1000，如下表定义所示:

MariaDB has virtual columns that allow one to automatically perform actions on other column data. For example, this is good if you want to display data in different units of metric measurement simply by dividing by 10 or 1000 as shown by the table definition below:

CREATE TABLE a (
     id INT NOT NULL AUTO_INCREMENT,
     distance_meters INT (11),
     distance_kilometers FLOAT (11,3) AS (distance_meters / 1000) VIRTUAL,
     PRIMARY KEY ( id )
) ENGINE=InnoDB;

但是，如何创建作为表列子集哈希的虚拟列?例如.下表(不起作用)旨在对姓名和薪水进行哈希处理，以便稍后通过与之前的哈希列表进行比较，我可以轻松地再次检查是否有任何员工的姓名或薪水发生了变化.我不想要整行的哈希.

However, how do I create a virtual column that is the hash of a subset of table columns? E.g. The table below (which doesnt work) aims to hash the name and salary, so that I can easily check again later if any of the employees names or salaries have changed by comparing against a previous hash list. I don't want the hash of the entire row.

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(200),
    salary INT(12),
    age int(3),
    hash VARCHAR(AS (MD5(concat(name, salary)))) PERSISTENT,
    PRIMARY KEY ( id )
) ENGINE=InnoDB;

推荐答案

该表达式对于 MariaDB 虚拟列是完全合法的.由于它是一个持久列，您甚至可以对其进行索引.

That expression is perfectly legal for MariaDB virtual columns. Since it is a persistent column, you could even index it.

顺便说一句，在您的示例中，在 VARCHAR 之后存在语法错误.

By the way, there is a syntax error in your example, after VARCHAR.

这篇关于MariaDB 虚拟列 - 我可以有哈希吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！