字段列表MariaDB中的未知列

Question

我正在使用PHPMyAdmin和MariaDB，并得到了标题中提到的这个奇怪的错误。

CALL log_total_outstanding(@message);
MySQL said: Documentation

#1054 - Unknown column 'time' in 'field list'

我在网上读过这个问题，可能是因为使用了"“而不是”“，或者是由于空白，但我没有这些问题。我在任何地方都没有所谓的“时间”专栏。

代码：

USE bank;

DROP TABLE IF EXISTS total_outstanding_log;
CREATE TABLE total_outstanding_log (
    total_outstanding DECIMAL(13,2) NOT NULL,
    log_time DATETIME NOT NULL
);

DELIMITER $
CREATE PROCEDURE IF NOT EXISTS insert_total_outstanding_log(
         IN total DECIMAL(13,2),
         OUT reply VARCHAR(50)) 
BEGIN
    
    INSERT INTO total_outstanding_log (total_outstanding,log_time)
           VALUES(total, CURRENT_DATE); -- Insert into log table
    SET REPLY = 'Total outstanding log inserted successfully '
            || CHAR(CURRENT_DATE); -- Set the out param message

END $
DELIMITER ;

DELIMITER $
CREATE PROCEDURE IF NOT EXISTS log_total_outstanding(OUT message VARCHAR(50)) 
BEGIN
    
    -- DECLARE @total_outstanding DECIMAL(13,2);
                -- Used to store total outstanding amount
    
    SELECT
        SUM(a.opening_balance)
        +
        (SELECT SUM(amount)
        FROM transaction WHERE isIncoming)
        -
        (SELECT SUM(Amount)
        FROM transaction WHERE isOutgoing) AS total_outstanding
        INTO @total_outstanding -- Assign the value to declared variable
    FROM account a;
    
    -- Call inserting log procedure (nested)
    CALL insert_total_outstanding_log(@total_outstanding, message);

END $
DELIMITER ;

CALL log_total_outstanding(@message);
SELECT @message; -- Display total oustanding log message

这个

SELECT
        SUM(a.opening_balance)
        +
        (SELECT SUM(amount)
        FROM transaction WHERE isIncoming)
        -
        (SELECT SUM(Amount)
        FROM transaction WHERE isOutgoing) AS total_outstanding
        INTO @total_outstanding -- Assign the value to declared variable
    FROM account a;

我已经测试过了。

Answer 1

||不是用于MySQL/MariaDB中的字符串连接。使用CONCAT(..., ...)函数。以下是MySQL所说的话：

mysql> select 'Total outstanding log inserted successfully '             || CHAR(CURRENT_DATE);
+----------------------------------------------------------------------------------+
| 'Total outstanding log inserted successfully '             || CHAR(CURRENT_DATE) |
+----------------------------------------------------------------------------------+
|                                                                                0 |
+----------------------------------------------------------------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings
    -> ;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Total outstanding log inserted successfully '                      |
| Warning | 1292 | Truncated incorrect DOUBLE value: '\x014\x88\xCE'                                                     |
+---------+------+-------------------------------------------------------------------------------------------------------+