mysql - Sql自动输入我连接的列

Question

我有 Fname 和 Lname 以及全名列。

我想创建一个函数，当我输入 Fname 和 Lname 时，它​​会出现在“全名”列中。

CREATE DEFINER=`root`@`localhost` FUNCTION `inser_customer`(Cname VARCHAR(50), CMname VARCHAR(50),CLname VARCHAR(50), edad VARCHAR(50), sex VARCHAR(50), brgay VARCHAR(50), sity VARCHAR(50), Cno VARCHAR(50), gmail VARCHAR(50)) RETURNS VARCHAR(50) CHARSET latin1
BEGIN
SET @full=(SELECT GROUP_CONCAT(Fname,' ', Lname) FROM customer);

IF EXISTS (SELECT * FROM customer WHERE Fname = Cname AND Lname = CLname)THEN
RETURN "Customer Already Exists";
ELSE INSERT INTO customer (`Fname`,`Mname`,`Lname`,`Birthdate`,`Gender`,`Brgy`,`City`,`ContactNo`,`Email`,`Full Name`)VALUES(Cname, CMname, CLname, edad, sex, brgay, sity, Cno, gmail, @full2);
RETURN "OK";
END IF;
    END$$

谢谢。

Answer 1

您需要为此使用触发器。

See here

A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.

插入新行后

delimiter //
CREATE TRIGGER full_name_after_insert AFTER INSERT ON customer
FOR EACH ROW
SET NEW.`Full Name` = CONCAT(NEW.`Fname`, " ",NEW.`Lname`)
END;//
delimiter ;

我建议您在更新现有行后使用触发器，以实现更好的一致性和维护

delimiter //
CREATE TRIGGER full_name_after_update AFTER UPDATE ON customer
FOR EACH ROW
SET NEW.`Full Name` = CONCAT(NEW.`Fname`, " ",NEW.`Lname`)
END;//
delimiter ;