使用纯ruby的Mysql浮点值插入

Question

我有一个如下所示的 ruby​​ 代码:

client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => "root")
client.query("CREATE TABLE Customers
                      (
                        Id INT PRIMARY KEY AUTO_INCREMENT,
                        Name VARCHAR(25),
                        CurrentDate DATE,
                        SourceCurrency VARCHAR(25),
                        TargetCurrency VARCHAR(25),
                        SourceAmount FLOAT(23,7),
                        TargetAmount FLOAT(23,7)
                      )"
                    )

    target_amount = source_amount * 0.7477

    client.select_db "project6"
    client.query("INSERT INTO Customers (Name, CurrentDate, SourceCurrency, TargetCurrency, SourceAmount, TargetAmount)VALUES(#{client.escape(customer_name)}, #{(Date.today)}, 'EUR', 'GBP', #{(source_amount)}, #{(target_amount)})")
    client.close

这里source_amount和target_amount都是float。在我的 project6 表中，SourceAmount 和 TargetAmount 的数据类型为 float(23,7)。

但是每当我运行此代码时就会发生错误:

Uncaught exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '23:39:43 +0600, 'EUR', 'GBP', 120.0, 89.724)' at line 1

如果我设置customer_name=“emu”则出现其他错误

Unknown column 'emu' in 'field list' (Mysql2::Error)

出了什么问题？

Answer 1

有几个问题。

(1) 您正在提供 Time.now 的输出(默认情况下，它是一天中的时间加上带有空格分隔符的时区偏移量，例如，23:39:43 +0600) 到 SQL DATE 属性，但不匹配。您需要提供日期。

(2) Name 属性的值需要加引号。否则，SQL 认为您引用的是属性名称。因此，如果您有 customer_name = 'emu'，则当前查询仅获取 emu 作为值，而不是 'emu'。您可以通过几种不同的方式解决这个问题。例如:

client.query("INSERT INTO Customers (Name, CurrentDate, SourceCurrency, TargetCurrency, SourceAmount, TargetAmount) VALUES ('#{client.escape(customer_name)}', #{(Date.today)}, 'EUR', 'GBP', #{(source_amount)}, #{(target_amount)})")

或者...

client.query("INSERT INTO Customers (Name, CurrentDate, SourceCurrency, TargetCurrency, SourceAmount, TargetAmount) VALUES ('" + client.escape(customer_name) + "', #{(Date.today)}, 'EUR', 'GBP', #{(source_amount)}, #{(target_amount)})")