CREATE TABLE AS 时 MySQL 截断了不正确的双精度值
我有一个简单的表，称为feed
| ID | 价格 |
|---|---|
| 1 | 20,00 |
| 2 | 30,00 |
| 3 | 40,00 |
| 4 | abcdef |
| 5， |
其中价格为varchar列。这很重要。现在，我可以做一个简单的查询，例如
select *从feed where price＆gt; 20，并且由于MySQL的隐式类型而起作用。
但是如果我做这个查询 创建表结果作为选择ID，价格从feed where where＆gt; 20它在截断的不正确的双值中失败：20,00
即使我将cast（价格为DECIMAL）放在选择和何处的查询中，它仍然失败。
如何抑制此错误或修复该错误？
I have a simple table called feed
| id | price |
|---|---|
| 1 | 20,00 |
| 2 | 30,00 |
| 3 | 40,00 |
| 4 | abcdef |
| 5 |
Where price is a VARCHAR column. This is important. Now I can do a simple query such as
select * from feed where price > 20 and it works, because of implicit type casting by mysql.
But if I do this querycreate table results as select id, price from feed where price > 20 It fails with truncated incorrect DOUBLE value: 20,00
Even if I put cast(price as decimal) in the query in both the SELECT and WHERE, it still fails.
How can I suppress this error or fix it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我遇到了一个类似的问题，您可能会在文档中找到用于创建表的解决方案： mariaiaDB-创建表
Mariadb从MySQL〜2009分叉，因此它相似，尤其是在旧功能上，
您可以指定特定的列以将其铸造为某种数据类型：
似乎有点命中和错过，我有一个7列表（我有一个7列表（ 3个varchar，4个ints），效果很好 - 新表的ints创建为double（17,0）。
另一个表我有34列的所有列，即使我将其降低到8个小int列，它也一直在
错误这也是首先制作表，然后插入忽略它的选择，直到它变得更加稳定为止。
I was having a similiar issue, you might find the solution in the docs for CREATE TABLE: MariaDB - Create Table
MariaDB forked from MySQL ~2009 so it is similar especially on the older functions
You can specify specific columns to cast them as a certain data type:
It seems a bit hit and miss, I had a 7 column table (3 varchar, 4 Ints) that worked fine - the New table had the Ints created as Double(17,0).
The other table I had 34 columns of all-sorts and it just kept erroring, even when I reduced it down to 8 tiny int columns, so I gave in and used the create code to make it and INSERT IGNORE INTO the data into it
So that is also an option to just make the table first and then INSERT IGNORE into it until this gets more stable.