如何从我的 SQL 中同一个表中的其他两个值创建一个值? [英] How can I create a value from two other values in the same table in my SQL?

问题描述

我在 SQL 数据库中有一个名为orders"的表:

I have a table named "orders" in an SQL database:

id  date   name  orderID
========================
1   1502   John    ?
2   1502   Jane    ?

id"设置为 AUTO_INCREMENT，日期"在我通过 php 表单插入数据时创建:

The "id" is set to AUTO_INCREMENT and the "date" is created when I insert data via my php form:

if ( !empty($_POST)) {
        $name = $_POST['name'];
        $valid = true;


        if ($valid) {
            $pdo = Database::connect();
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $sql = "INSERT INTO orders (name,date) values(?,CURDATE())";
            $q = $pdo->prepare($sql);
            $q->execute(array($name));
            Database::disconnect();
        }
}

我希望从值id"和date"自动创建orderID"的值:

I wish that the value for "orderID" is created automatically from the values "id" and "date":

id  date   name  orderID
========================
1   1502   John    15021
2   1502   Jane    15022

推荐答案

由于 id 是 AUTO_INCREMENT 这一事实，您无法处理此值.但这很简单.向表中添加一个 AFTER INSERT 触发器，然后让它更新 orderID 列.

Due to the fact, that the id is an AUTO_INCREMENT you have no hand on this value. But it's quite simple. Add a AFTER INSERT trigger to the table and let it update the orderID column afterwards.

这里是 AFTER UPDATE 触发器的快速示例.

Here a quick example of a AFTER UPDATE trigger.

CREATE TRIGGER yourTrigger
AFTER INSERT
   ON yourTable FOR EACH ROW

BEGIN
    UPDATE yourTable
    SET orderid = CAST(date as char(4)) + CAST(id as char(1))
    WHERE ID = NEW.id

END; 

这篇关于如何从我的 SQL 中同一个表中的其他两个值创建一个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！