mysql - 如何使用 ALTER 语句与 GENERATE ALWAYS INSIDE mysql 函数？

Question

json 数组驻留在我的表产品的 Product_details 列中

{
"email":"test@test.com",
"street_address#1":"street1",
"street_address#2":"street2",
"given_name":"test",
"products": [ 
                { "product": "product1", "status": "available"},
                { "product": "product2", "status": "not-available"} 
            ]
}

我的 mysql 函数中的 alter table 不起作用。

感谢任何帮助。

mysql函数如下:

DROP FUNCTION IF EXISTS CREATE_COLUMN;
CREATE FUNCTION CREATE_COLUMN(PRODUCT_ID INT) 
RETURNS MEDIUMTEXT
BEGIN
    DECLARE PRODUCTS, STATUS MEDIUMTEXT;
    DECLARE SINGLE_PRODUCT, SINGLE_STATUS, COL_NAME, COL_VALUE MEDIUMTEXT;
    DECLARE indx INT DEFAULT 0;
        SELECT json_unquote(json_extract(product_details, '$.products[*].product')) INTO PRODUCTS FROM product WHERE product_id = PRODUCT_ID;
        SELECT json_unquote(json_extract(product_details, '$.products[*].status')) INTO STATUS FROM product WHERE product_id = PRODUCT_ID;

        REPEAT
            SET SINGLE_PRODUCT = JSON_EXTRACT(PRODUCTS, CONCAT("$[", indx, "]"));
            SET SINGLE_STATUS = JSON_EXTRACT(STATUS, CONCAT("$[", indx, "]"));
            SET COL_NAME = CONCAT('"','view_products_', TRIM(BOTH '"' FROM SINGLE_PRODUCT), '"');
            SET COL_VALUE = SINGLE_STATUS;

        # This query is not working
            ALTER TABLE product ADD COLUMN COL_NAME varchar(127) GENERATED ALWAYS AS (COL_VALUE) VIRTUAL;

            SET indx = indx + 1;
            UNTIL indx = JSON_LENGTH(PRODUCTS)
       END REPEAT;
    RETURN COL_NAME;
END;

SELECT CREATE_COLUMN (001);

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html说:

SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers.

https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html说:

The following additional statements or operations are not permitted within stored functions.

• Statements that perform explicit or implicit commit or rollback.

(ALTER TABLE 执行隐式提交。)

我不知道你想做什么，但你需要考虑一种不同的方法。

我建议您不要以这种方式使用 JSON。将产品数据存储在普通列中，您就不必使用生成的列执行所有这些复杂的操作。