MySQL 5 中的計算/生成列

如何在 MySQL 版本 5 中創建帶有公式列的表？

例如：我有一個名為 的表Product，它有 5 列：

id int,
amount int,
sold_out int,
faulty int,
remain_amount is (amount-(sold_out+faulty))

我不會使用觸發器或視圖。

我正在尋找一種類似於 SQL Server 中稱為計算列的解決方案。

等待支持生成欄位的 5.7

mysql> CREATE TABLE sales ( 
   -> name VARCHAR(20),  
   -> price_eur DOUBLE,
   -> amount INT,
   -> total_eur DOUBLE AS (price_eur * amount),
   -> total_usd DOUBLE AS (total_eur * xrate),
   -> xrate DOUBLE);
Query OK, 0 rows affected (0,16 sec)

http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/

或者使用 MariaDB，其中虛擬/計算列現在正在工作。這些是 MySQL 5.7 中的一個beta特性。

你可以用一個簡單的TRIGGER. MySQL 支持BEFORE INSERT動作觸發器。您只需要NEW.remain_amount=(NEW.amount-(NEW.sold_out+NEW.faulty));在觸發器中分配，僅此而已。*****

表結構：

CREATE TABLE test.Product (
 id INT NOT NULL AUTO_INCREMENT,
 amount DECIMAL(10,2) NULL,
 sold_out DECIMAL(10,2) NULL,
 faulty DECIMAL(10,2) NULL,
 remain_amount DECIMAL(10,2) NULL,
 PRIMARY KEY (id));

扳機：

USE test;

DELIMITER $$

DROP TRIGGER IF EXISTS test.Product_BEFORE_INSERT$$
USE test$$
CREATE DEFINER = CURRENT_USER TRIGGER test.Product_BEFORE_INSERT BEFORE INSERT ON Product FOR EACH ROW
BEGIN
SET NEW.remain_amount=(NEW.amount-(NEW.sold_out+NEW.faulty));
END
$$
DELIMITER ;

測試：

mysql> CREATE TABLE test.Product (
   ->   id INT NOT NULL AUTO_INCREMENT,
   ->   amount DECIMAL(10,2) NULL,
   ->   sold_out DECIMAL(10,2) NULL,
   ->   faulty DECIMAL(10,2) NULL,
   ->   remain_amount DECIMAL(10,2) NULL,
   ->   PRIMARY KEY (id));
Query OK, 0 rows affected (0.01 sec)

mysql>   USE test;
Database changed
mysql> 
mysql> DELIMITER $$
mysql> DROP TRIGGER IF EXISTS test.Product_BEFORE_INSERT$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> USE test$$
Database changed
mysql> CREATE DEFINER = CURRENT_USER TRIGGER test.Product_BEFORE_INSERT BEFORE INSERT ON Product FOR EACH ROW
   -> BEGIN
   -> SET NEW.remain_amount=(NEW.amount-(NEW.sold_out+NEW.faulty));
   -> END
   -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> SELECT * FROM test.Product;
Empty set (0.00 sec)

mysql> INSERT INTO test.Product
   -> (id,amount,sold_out,faulty,remain_amount)
   -> VALUES
   -> (1,100,50,30,0);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM test.Product;
+----+--------+----------+--------+---------------+
| id | amount | sold_out | faulty | remain_amount |
+----+--------+----------+--------+---------------+
|  1 | 100.00 |    50.00 |  30.00 |         20.00 |
+----+--------+----------+--------+---------------+
1 row in set (0.00 sec)

mysql> 

---

*：嗯，不完全是。您還必須創建一個UPDATEtirgger。

引用自：https://dba.stackexchange.com/questions/117600