Computed/Generated columns in MySQL 5

Wait for 5.7 where generated fields are supported

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/

Or use MariaDB, where virtual/computed columns are working now. These are a beta feature in MySQL 5.7.

You can do it with a simple TRIGGER. MySQL supports BEFORE INSERT action trigger. You just have to assign to NEW.remain_amount=(NEW.amount-(NEW.sold_out+NEW.faulty)); in the trigger and that's all.^*

TABLE STRUCTURE:

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));

TRIGGER:

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 ;

TEST:

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> 

^{*: Well, not exactly. You also have to create an UPDATE tirgger as well.}

MySQL 5.7 supports Generated Columns and 5.6 doesn't.

Generated Columns. MySQL now supports the specification of generated columns in CREATE TABLE and ALTER TABLE statements. Values of a generated column are computed from an expression specified at column creation time. Generated columns can be virtual (computed “on the fly” when rows are read) or stored (computed when rows are inserted or updated). For more information, see Section 13.1.18.7, “CREATE TABLE and Generated Columns”.

All you need to do is to follow the documentation.

This is a really simple table to match what's in your question on MySQL 5.7.36.

CREATE TABLE `Product` (
  `id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `sold_out` int(11) DEFAULT NULL,
  `faulty` int(11) DEFAULT NULL,
  `remain_amount` int(11) GENERATED ALWAYS AS ((`amount` - (`sold_out` + `faulty`))) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT Product VALUES(1, 10, 4, 1, DEFAULT);
INSERT Product (id, amount, sold_out, faulty) VALUES (2, 5, 4, 1);

SELECT * FROM Product;
+------+--------+----------+--------+---------------+
| id   | amount | sold_out | faulty | remain_amount |
+------+--------+----------+--------+---------------+
|    1 |     10 |        4 |      1 |             5 |
|    2 |      5 |        4 |      1 |             0 |
+------+--------+----------+--------+---------------+
2 rows in set (0.00 sec)

Note that MySQL 5.7 supports 2 types of Generated Columns depending on your use case:

• VIRTUAL, values are not stored, it is evaluated on reads.
• STORED, values are stored on writes (inserts and updates).

The default is VIRTUAL if neither keyword is specified.

2nd Note, DEFAULT is the only permitted value for the Generated Columns, or you have to exclude the generated column.

That's why the insert statements are like this in the above example

INSERT Product VALUES(1, 10, 4, 1, DEFAULT);
# or
INSERT Product (id, amount, sold_out, faulty) VALUES (2, 5, 4, 1);

Read more about the details here