MySQL中INSERT INTO语句报错求助：建表成功但插入数据失败

错误原因 #

• 使用了中文全角引号（‘’），MySQL仅支持英文半角引号（''）作为字符串边界符。
• 多处存在引号未闭合的情况，比如BlueMoon、Lime_fizz的directions字段值。
• Ohmygosh记录的字段值数量不匹配：原语句给出7个值，但表仅包含6个字段，需合并重复的directions内容。
• lone tree记录的drink_name字段引号未正确闭合。

修正后的INSERT语句 #

INSERT INTO easy_drinks(drink_name,main,amount1,second, amount2,directions) 
VALUES
('Black thorn','tonicwater',1.5 ,'pineapple_juice',1 ,'stir with ice,strain into cocktail glass with lemon twist'),
('BlueMoon','soda',1.5,'blueberry_juice',0.75, 'strain into cocktail glass with lemon twist'),
('Ohmygosh','peach_nectar',1,'pineapple_juice',1,'stir with ice, strain into shot glass'),
('Lime_fizz','sprite',1.5,'lime_juice',0.75,'stir with ice,strain into a cocktail glass'),
('kiss on lips','cherry juice',2,'apricot nector',7,'serve over ice with straw'),
('hot gold','peach nectar',3,'orange juice',6,'pour hot orange juice in mug and peach nectar'),
('lone tree','soda',1.5,'cherry juice',0.75,'stir with ice,strain into cocktail glass'),
('greyhound','soda',1.5,'grapefruit juice',5,'serve with ice,stir well'),
('indian summer','apple juice',2,'hot tea',6,'add juice to mug and topoff with hot tea'),
('bullfrog','iced tea',1.5,'lemonade',5,'serve over ice with lime slice'),
('soda and it','soda',2,'grape juice',1,'shake in cocktail glass,no ice');

验证说明 #

修正后的语句解决了所有语法问题：

1. 统一替换为英文半角引号
2. 补全所有未闭合的引号
3. 调整Ohmygosh的directions字段值，确保字段数量匹配
4. 修复lone tree的引号闭合问题

执行该语句即可成功插入所有数据。

内容的提问来源于stack exchange，提问作者Digvijay