Mysql

MariaDB 欄位列表中的未知列

  • January 11, 2022

我正在使用 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;

我已經測試過是否單獨工作。

||不適用於 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'                                                     |
+---------+------+-------------------------------------------------------------------------------------------------------+

引用自:https://dba.stackexchange.com/questions/305888