MySQL + Dapper扩展:SQL语法错误 [英] MySQL + Dapper Extensions: Error in SQL syntax

问题描述

我正在尝试使用Dapper Extensions进行CRUD操作.但是在将数据插入MySQL数据库时出现如下错误:

I am trying to do CRUD operations using Dapper Extensions. But I am getting error while inserting data to MySQL database as follows:

错误:您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以获取在[...]行[....]

Error: 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 [......] at line [....]

如果我使用MSSQL数据库，则Dapper Extensions正常运行.为什么我的MySQL出现此错误?

If I use MSSQL database, Dapper Extensions is working correctly. Why I am getting this error with MySQL?

推荐答案

该错误是因为Dapper Extensions正在生成SQL Server的查询(默认情况下)，而您实际上已连接到MySQL.这两个RDBMS之间存在语法差异，因此会出现错误.您必须告诉Dapper Extensions您正在连接到MySQL.

The error is because Dapper Extensions is generating the query for SQL server (by default) where as you are actually connected to MySQL. There are syntax differences between these two RDBMSs and hence the error. You have to tell Dapper Extensions that you are connecting to MySQL.

在应用程序启动时在某处设置方言".

Set the Dialect at the startup of your application somewhere.

//Synchronous
DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();

//Asynchronous
DapperExtensions.DapperAsyncExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect();

您可以注意到，您需要针对同步和异步方法分别进行配置.您可以在 github 上了解有关此内容的更多信息.

As you can note, you need to configure this separately for synchronous and asynchronous methods. You can read more about this on github.

这将指示Dapper Extensions根据MySql的语法生成查询.对于许多支持多个RDBMS生成查询的ORM而言，不仅Dapper Extensions也是必需的.

This will instruct Dapper Extensions to generate the queries according to the syntax of MySql. Not only Dapper Extensions, similar is necessary for many ORMs those support query generation for multiple RDBMSs.

除此之外，您还可以考虑实施日志记录，这可以帮助您诊断问题. MiniProfiler 是用于此目的的好工具.您可以在我的其他答案中找到更多详细信息.

Apart from this, you may also consider implementing logging which may help you diagnose the issues. MiniProfiler is good tool for this purpose. You may find more details in my other answer.

这篇关于MySQL + Dapper扩展:SQL语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！