为什么我收到警告：截断错误的DOUBLE值：'％asian％' [英] Why am I getting the warning: truncated incorrect DOUBLE value: '%asian%'

问题描述

我正在尝试运行此查询：



SELECT r.restaurant_name从餐馆作为r，种族作为e，过敏原作为WHERE e.restaurant_id = a。 restaurant_id和e.restaurant_id = r.restaurant_id和a.restaurant_id = r.restaurant_id像a.allergen ='％fish％'喜欢e.ethnicity ='％asian％';



如果它有助于这是此查询中所有表格的架构：



DESCRIBE种族;

+ --- ------------ + -------------- + ------ + ----- + --------- + ------- +

|领域|输入|空|钥匙|默认|额外的|

+ --------------- + -------------- + ------ + - ---- + --------- + ------- +

| restaurant_id | int（11）|没有| MUL | NULL | |

|种族| varchar（255）|没有| | NULL | |

+ --------------- + -------------- + ------ + - --- + --------- + ------- +



DESCRIBE餐厅;

+ ----------------- + -------------- + ------ + ----- + ---- ----- + ---------------- +

|领域|输入|空|钥匙|默认|额外|

+ ----------------- + -------------- + ------ + ----- + --------- + ---------------- +

| restaurant_id | int（11）|没有| PRI | NULL | auto_increment |

| restaurant_name | varchar（255）|是的| | NULL | |

| phone_number | varchar（255）|是的| | NULL | |

| address_1 | varchar（255）|是的| | NULL | |

| address_2 | varchar（255）|是的| | NULL | |

|城市| varchar（255）|是的| | NULL | |

|州| varchar（2）|是的| | NULL | |

| zip | int（11）|是的| | NULL | |

|文件名| varchar（255）|是的| | NULL | |

|图片| longblob |是的| | NULL | |

+ ----------------- + -------------- + ------ + ----- + --------- + ---------------- +



DESCRIBE过敏原;

+ --------------- + -------------- + ------ + - ---- + --------- + ---------------- +

|领域|输入|空|钥匙|默认|额外的|

+ --------------- + -------------- + ------ + - ---- + --------- + ---------------- +

|过敏原| varchar（255）|是的| | NULL | |

| restaurant_id | int（11）|是的| MUL | NULL | |

| menu_item_id | int（11）|是的| MUL | NULL | |

| allergen_id | int（11）|没有| PRI | NULL | auto_increment |

+ --------------- + -------------- + ------ + - ---- + --------- + ---------------- +



我尝试了什么：



我尝试运行单独的查询（将查询分成更小的部分）以查找此查询的哪个部分无法正常工作但每个部分都很好。



我查看了我得到的错误，我发现的是这通常在使用不必要的AND或撇号丢失时给出，只有问题不是这样的我的查询。



非常感谢MYSQL中经验丰富或知识渊博的人的帮助：）

解决方案

您在查询中随机 LIKE

 D a.restaurant_id = r.restaurant_id  LIKE  a.allergen ='％fish％'

尝试将它们更改为 AND



这也是一种非常老式的表格连接方式。请参阅 SQL连接的可视化表示 [ ^ ]和在SQL中加入表格 [ ^ ]

例如

  SELECT  r.restaurant_name  FROM  #restaurant  as  r 
  LEFT   JOIN  #ethnicity  as  e < span class =code-keyword> ON  e.restaurant_id = r.restaurant_id 
  LEFT  加入 #allergen  as  a  ON  a.restaurant_id = r.restaurant_id  AND  e.restaurant_id = a.restaurant_id 
  WHERE  a.allergen = ' ％fish ％' 和 e.ethnicity = '  ％亚洲％'; 

I am trying to run this query:

SELECT r.restaurant_name FROM restaurant as r, ethnicity as e, allergen as a WHERE e.restaurant_id=a.restaurant_id AND e.restaurant_id=r.restaurant_id AND a.restaurant_id=r.restaurant_id LIKE a.allergen='%fish%' LIKE e.ethnicity='%asian%';

If it helps this is the schema of all the tables in this query:

DESCRIBE ethnicity;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| restaurant_id | int(11) | NO | MUL | NULL | |
| ethnicity | varchar(255) | NO | | NULL | |
+---------------+--------------+------+-----+---------+-------+

DESCRIBE restaurant;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| restaurant_id | int(11) | NO | PRI | NULL | auto_increment |
| restaurant_name | varchar(255) | YES | | NULL | |
| phone_number | varchar(255) | YES | | NULL | |
| address_1 | varchar(255) | YES | | NULL | |
| address_2 | varchar(255) | YES | | NULL | |
| city | varchar(255) | YES | | NULL | |
| state | varchar(2) | YES | | NULL | |
| zip | int(11) | YES | | NULL | |
| filename | varchar(255) | YES | | NULL | |
| image | longblob | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+

DESCRIBE allergen;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| allergen | varchar(255) | YES | | NULL | |
| restaurant_id | int(11) | YES | MUL | NULL | |
| menu_item_id | int(11) | YES | MUL | NULL | |
| allergen_id | int(11) | NO | PRI | NULL | auto_increment |
+---------------+--------------+------+-----+---------+----------------+

What I have tried:

I have tried running separate queries (broke the query into smaller parts) to find which part of this query is not working but every part works fine.

I looked up the error I am getting and what I found was this is usually given when unnecessary AND's are used or apostrophes are missing, only problem is that's not the case for my query.

Would appreciate the help of a seasoned or knowledgable person in MYSQL :)

解决方案

You have random "LIKE"s in your query

D a.restaurant_id=r.restaurant_id LIKE a.allergen='%fish%' 

Try changing them to AND

That is also a very old fashioned way to join tables. See Visual Representation of SQL Joins[^] and Joining Tables in SQL[^]
E.g.

SELECT r.restaurant_name FROM #restaurant as r
LEFT JOIN #ethnicity as e ON e.restaurant_id=r.restaurant_id
LEFT JOIN #allergen as a ON a.restaurant_id=r.restaurant_id AND e.restaurant_id=a.restaurant_id
WHERE a.allergen='%fish%' and e.ethnicity='%asian%';

这篇关于为什么我收到警告：截断错误的DOUBLE值：'％asian％'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！