殊途同归，ado.net快速实现MySql的CRUD

正在学习MySql编程,安装了官方的C#客户端，在自己的机器上写了个demo，将可能常见且容易出现的问题或错误温故知新一遍，写下来以备日后再用。

一、MySql的连接字符串

连接字符串无比简单，但是这是必须的。我们需要关心的是，通过.net客户端程序，mysql的连接字符串可以像sql server一样，利用连接池高效管理连接。简单配置如下：

  <connectionStrings>
    <add name="MySqlConnString" connectionString="Server =localhost; Database =test; Uid =***; Pwd =***;Pooling=true; Max Pool Size=20;Min Pool Size=10;Allow Batch=true;" providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>

http://www.connectionstrings.com/mysql

二、实现简单的CRUD

毫无疑问，快速上手CRUD的前提是必须熟悉基础的sql，然后再对照mysql的特定语法做特定操作。不要小看了这简单的crud，普通应用系统中这部分的工作占了很大一部分，笔者才疏学浅，为这部分工作也花过不少时间。还好我们现在有了各种优秀的orm，相对而言实现起来比较轻松。

1、插入记录

最简单的方式，当然是传入参数一条一条插入：

addpersonCREATE DEFINER=`root`@`localhost` PROCEDURE `AddSimplePerson`(
FirstName varchar(16),
LastName varchar(16),
Age int,
CreateDate datetime,
UpdateDate datetime,
State int,
Remark varchar(256)
)
BEGIN
INSERT INTO test.Person(FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark)
VALUES(FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark);

SELECT @@IDENTITY;

END

经测试，插入数据的存储过程里的变量定义可以和列名一模一样。基于mysql的特殊语法，建议参数变量不要和列名一模一样，最好给个别名，这里不是介绍的重点，略过。

和插入记录比较类似的一个功能就是复制表定义及复制数据行，曾经简单总结过，不再赘述。

插入记录还有一个比较常用的功能，就是批量插入。

记得在向Sql Server批量插入的时候，可以通过SqlBulkCopy实现批量快速插入。先使用SqlBulkCopy测试了下MySql的批量插入，执行的结果就是直接如下抛出异常：

“在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且 SQL Server 已配置为允许远程连接。 (provider: Named Pipes Provider, error: 40 - 无法打开到 SQL Server 的连接)”

在配置文件中将连接字符串加上providerName（providerName="MySql.Data.MySqlClient"）依然如故。看到这个结果忍不住哀号一句，老子tmd是要向mysql插入数据啊。

无奈打开msdn，看到SqlBulkCopy类的功能介绍直接崩溃：“lets you efficiently bulk load a SQL Server table with data from another source.”。原来SqlBulkCopy只对SQL Server有效啊，这点从它的命名空间（System.Data.SqlClient）大致可以猜到，MS真不厚道。

那么到底怎么实现批量插入的功能呢？

利用ILSpy查看了一下C#客户端的源码，发现有个MySqlBulkLoader，查看示例竟然是从文件中批量操作数据的，这个明显不满足我们的开发要求。话说不就是一次多插入几条数据吗？至于真的要先写入文件吗？

最后在网上搜索了一下，看到iteye上的一篇Java 批量插入数据库(MySQL)数据，仔细分析这篇文章，个人认为Java下实现批量插入无非就是通过JDBC API或者PreparedStatement批处理方式，实现思想还是比较简单的。其实.net也有自己的api实现批量插入（SqlBulkCopy），只是不支持mysql而已；而批处理命令（PreparedStatement）我怎么看上去都像是拼接字符串呢？得知mysql和db2支持下面这种多行插入（multi row insert）：

insert2personsINSERT INTO test.Person(FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark)
VALUES('jeff','wong',28,now(),now(),1,'hello mysql'),
('terry','liang',26,now(),now(),1,'hello mysql,too')

我立刻想到实现批量插入的一种简单方式（也就是他的文章里提到的伪批处理命令），需要拼接sql字符串。贴一下有sql注入风险的示例代码：

batchinsert1        /// <summary>
        /// 拼接字符串批量插入
        /// </summary>
        /// <param name="sqlString">sql插入语句，形如INSERT INTO test.Person(FirstName) VALUES或 INSERT INTO test.Person(FirstName)</param>
        /// <param name="columes">插入的列数</param>
        /// <param name="paramValues">需要插入的值</param>
        /// <param name="strConnection">数据库连接字符串</param>
        /// <returns></returns>
        public static int BatchInsert(string sqlString, int columes, object[] paramValues, string strConnection)
        {
            if (paramValues.Length % columes != 0)
            {
                throw new ArgumentException("参数个数有误");
            }
            StringBuilder sb = new StringBuilder();
            sb.Append(sqlString);
            if (sqlString.LastIndexOf(" VALUES", StringComparison.OrdinalIgnoreCase) == -1)
            {
                sb.Append(" VALUES ");
            }
            int rows = paramValues.Length / columes;
            for (int i = 0; i < rows; i++)
            {
                sb.Append("(");
                for (int j = 0; j < columes; j++)
                {
                    sb.AppendFormat("'{0}'", paramValues[columes * i + j]);
                    if (j != columes - 1)
                    {
                        sb.Append(",");
                    }
                }
                sb.Append("),");
            }
            int affectNum = ExecuteNonQuery(strConnection, sb.ToString().Trim(','), CommandType.Text, true);//拼接字符串批量插入
            return affectNum;
        }

调用如下：

testbatchinsert1      string sqlString = "INSERT INTO test.Person(FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark) VALUES";
            int columes = 7;//需要插入的列数
            var listObj = new List<object>();
            for (int i = 0; i < 30; i++)   //拼接字符串，批量插入30条记录
            {
                var paramVals = new object[] { string.Format("jeff{0}", columes * i + j), "wong", 28, DateTime.Now, DateTime.Now, 1, "this is a simple add person test" };
                listObj.AddRange(paramVals);
            }
            int affectNums = MySqlHelper.BatchInsert(sqlString, columes, listObj.ToArray(), strSqlConn);
            Console.WriteLine(affectNums);

通过上面代码可以看到，拼接字符串的地方没有进行特殊字符串处理，很容易造成sql注入。虽然实际开发的时候可以通过一个字符串处理函数进行sql敏感字符串处理，但显然这不是最优的方式。我们完全可以参数化传参再插入，重新改进代码如下：

batchinsert2        /// <summary>
        /// 拼接字符串批量插入(安全插入)
        /// </summary>
        /// <param name="sqlString">sql插入语句，形如INSERT INTO test.Person(FirstName) VALUES或 INSERT INTO test.Person(FirstName)</param>
        /// <param name="columes">插入的列数</param>
        /// <param name="paramValues">需要插入的值</param>
        /// <param name="strConnection">数据库连接字符串</param>
        /// <returns></returns>
        public static int BatchInsert(string sqlString, int columes, object[] paramValues, string strConnection)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(sqlString);
            if (sqlString.LastIndexOf(" VALUES", StringComparison.OrdinalIgnoreCase) == -1)
            {
                sb.Append(" VALUES ");
            }
            var listParamKeys = new List<string>();//参数的键值
            string paramKey = string.Empty;
            int rows = paramValues.Length / columes;
            for (int i = 0; i < rows; i++) //拼接参数
            {
                sb.Append("(");
                for (int j = 0; j < columes; j++)
                {
                    paramKey = string.Format("@v_{0}", columes * i + j); //参数前必须加入@
                    sb.Append(paramKey);
                    listParamKeys.Add(paramKey);
                    if (j != columes - 1)
                    {
                        sb.Append(",");
                    }
                }
                sb.Append("),");
            }
            string sqlText = sb.ToString().Trim(',') + ";";
            int affectNum = ExecuteNonQuery(strConnection, sqlText, CommandType.Text, PrepareParameters(listParamKeys.ToArray(), paramValues), true);//拼接字符串批量插入
            return affectNum;
        }

        /// <summary>
        /// 拼接字符串批量插入
        /// </summary>
        /// <param name="sqlString">sql插入语句，形如INSERT INTO test.Person(FirstName) VALUES或 INSERT INTO test.Person(FirstName)</param>
        /// <param name="columes">插入的列数</param>
        /// <param name="paramValues">需要插入的值</param>
        /// <param name="strConnection">数据库连接字符串</param>
        /// <param name="isSafe">是否直接拼接字符串安全插入</param>        
        /// <returns></returns>
        public static int BatchInsert(string sqlString, int columes, object[] paramValues, string strConnection, bool isSafe)
        {
            if (isSafe == true) //安全插入
            {
                return BatchInsert(sqlString, columes, paramValues, strConnection);
            }
            if (paramValues.Length % columes != 0)
            {
                throw new ArgumentException("参数个数有误");
            }
            StringBuilder sb = new StringBuilder();
            sb.Append(sqlString);
            if (sqlString.LastIndexOf(" VALUES", StringComparison.OrdinalIgnoreCase) == -1)
            {
                sb.Append(" VALUES ");
            }
            int rows = paramValues.Length / columes;
            for (int i = 0; i < rows; i++)
            {
                sb.Append("(");
                for (int j = 0; j < columes; j++)
                {
                    sb.AppendFormat("'{0}'", paramValues[columes * i + j]);
                    if (j != columes - 1)
                    {
                        sb.Append(",");
                    }
                }
                sb.Append("),");
            }
            int affectNum = ExecuteNonQuery(strConnection, sb.ToString().Trim(',') + ";", CommandType.Text, true);//拼接字符串批量插入
            return affectNum;
        }

调用方式如下：

testbatchinsert2   //int affectNums = MySqlHelper.BatchInsert(sqlString, columes, listObj.ToArray(), strSqlConn, false); //无参数化插入
            //Console.WriteLine(affectNums);

            int affectNums = MySqlHelper.BatchInsert(sqlString, columes, listObj.ToArray(), strSqlConn, true); //参数化插入
            Console.WriteLine(affectNums);

还是免不了拼接，而且可读性较差，参数较多，调用不够简洁。

那如何更好地批量插入呢？老实说，直到现在我也还是不知道。实际开发中，批量插入的方式通常都是按照实体列表进行插入(或者使用datatable批量插入)的。于是，我又重写了一个泛型方法，用来实现参数化的批量插入，当然，原理还是拼接字符串，对于常见数据库批量插入操作，我推荐你使用下面这种方式：

BATCHINSERT        /// <summary>
        /// 批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tbName">要插入的目标表名称</param>
        /// <param name="columeArr">要插入的列名数组</param>
        /// <param name="listModels">要插入的实体数组</param>
        /// <param name="strConnection">数据库连接字符串</param>
        /// <returns></returns>
        public static int BatchInsert<T>(string tbName, string[] columeArr, IList<T> listModels, string strConnection) where T : class, new()
        {
            if (listModels == null || listModels.Count == 0)
            {
                throw new ArgumentException("没有需要批量插入的数据");
            }
            int columes = columeArr.Length;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("INSERT INTO {0} ", tbName);
            AppendColumes(sb, columeArr);
            sb.Append(" VALUES ");
            var listParamKeys = new List<string>();//参数的键值
            string paramKey = string.Empty;
            for (int i = 0; i < listModels.Count; i++)  //构造参数
            {
                sb.Append("(");
                for (int j = 0; j < columes; j++)
                {
                    paramKey = string.Format("@v_{0}_{1}", columeArr[j], columes * i + j); //参数前必须加入@
                    sb.Append(paramKey);
                    listParamKeys.Add(paramKey);
                    if (j < columes - 1)
                    {
                        sb.Append(",");
                    }
                }
                sb.Append("),");
            }
            var listParamValues = new List<object>();
            for (int i = 0; i < listModels.Count; i++)  //构造参数值数组
            {
                FastPrepareParamValue<T>(listModels[i], columeArr, listParamValues);
            }
            string sqlText = sb.ToString().Trim(',') + ";";
            int affectNum = ExecuteNonQuery(strConnection, sqlText, CommandType.Text, PrepareParameters(listParamKeys.ToArray(), listParamValues.ToArray()), true);//拼接字符串批量插入
            return affectNum;
        }

        private static void AppendColumes(StringBuilder sb, string[] columeArr)
        {
            if (columeArr == null || columeArr.Length == 0)
            {
                throw new ArgumentException("插入列不能为空");
            }
            sb.Append("(");
            for (int i = 0; i < columeArr.Length; i++)
            {
                sb.Append(columeArr[i]);
                if (i < columeArr.Length - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(")");
        }

        private static void FastPrepareParamValue<T>(T model, string[] columeArr, List<object> listPramValues)
        {
            object objValue = null;
            var objType = model.GetType();
            var properties = objType.GetProperties(bf);
            foreach (var columeName in columeArr)
            {
                foreach (var propInfo in properties)
                {
                    if (string.Compare(columeName, propInfo.Name, true) != 0)
                    {
                        continue;
                    }
                    try
                    {
                        objValue = propInfo.FastGetValue(model);
                    }
                    catch
                    {
                        objValue = null;
                    }
                    finally
                    {
                        listPramValues.Add(objValue);
                    }
                    break;
                }
            }
        }

调用方式也比较简洁，如下所示：

TestBatchInsertModels          string[] columeArr = new string[] { "FirstName", "LastName", "Age", "CreateDate", "UpdateDate", "State", "Remark" };
            var listPersons = new List<Person>();
            Person person = null;
            for (int i = 0; i < 30; i++)
            {
                person = new Person
                {
                    FirstName = string.Format("jeff{0}", i.ToString()),
                    LastName = "wong",
                    Age = 28,
                    CreateDate = DateTime.Now,
                    UpdateDate = DateTime.Now,
                    State = 1,
                    Remark = string.Format("this is test person {0} ", i + 1)
                };
                listPersons.Add(person);
            }
            int affectNums = MySqlHelper.BatchInsert<Person>("test.Person", columeArr, listPersons, strSqlConn); //参数化插入
            Console.WriteLine(affectNums);

经测试，构造插入字符串的地方，参数前面必须加个“@”符号，如果不加，则抛出异常，这一点很奇怪。

必须指出，sql拼接执行插入的长度是有限制的。在我的电脑上测试插入3000个实体一点问题没有，当插入1万条记录的时候也还行，当插入10万条的时候……虽然我没有测试过可以执行的sql长度最长是多少，但是根据经验，这个sql总长度应该不会超过8k+的长度限制吧（存疑？？），所以开发中可以按照策略对需要批量操作的数据再分批量插入。

如果您有好的批量插入数据的思路和解决方案，请不吝赐教。

2、查询

数据库的两大作用，一个是存储，另一个就是各种查询了。所以必须重视查询，重视查询的形式当然是从熟悉查询语法开始，这里不做过分说明，大家可以参考官方说明文档。

简单查询如利用组合条件或者id主键查询记录，很简单：

querypersonsCREATE DEFINER=`root`@`localhost` PROCEDURE `QueryPersons`(
v_FirstName varchar(16),
v_State int
)
BEGIN
SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark
FROM test.Person
WHERE 1=1 
AND FirstName LIKE CONCAT('%',v_FirstName,'%') 
AND State=v_State;
END

在程序中调用非常简单，正确传参即可：

querypersons      var paramKeys = new string[] { "@v_FirstName", "@v_State" };
            var paramVals = new object[] { "ff", 1 };
            var listPerson = MySqlHelper.QueryForList<Person>("QueryPersons", CommandType.StoredProcedure, strSqlConn, paramKeys, paramVals);

注：mysql的字符串函数不同于sql server，最简单的字符串连接，mysql通常都是通过CONCAT函数，而sql server则用+号即可。

CONCAT函数在mysql编程中出现频率极高，它的用法如下：

CONCAT(str1,str2,…)

连接的字符串参数个数看上去原则上是无限制的，返回结果为连接参数产生的字符串。

它还有两个特性，记录一下：

（1）、如有任何一个参数为NULL ，则返回值为 NULL。

mysql> SELECT CONCAT('My', NULL, 'Sql');

-> NULL

（2）、CONCAT一个或多个参数，如果所有参数均为非二进制字符串，则结果为非二进制字符串；如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式。

mysql> SELECT CONCAT('My', 'Sql',2011);

-> BLOB

下面重点讨论一下分页查询的实现：

mysql的取前top n条记录有自己的语法，即limit n，这个在这一篇里曾经提及。limit还有一个偏移区间的写法:limit offset,n,第一个参数offset指定第一个返回记录行的偏移量，第二个参数n指定返回记录行的最大数目，举例来说：

mysql> SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15

还有一种方式就是指定第二个数字n为-1，那么它表示的意思就是查询从某一个偏移量到记录集结束的所有记录数：

mysql> SELECT * FROM table LIMIT 5,-1; // 检索记录行 6-last.

这种特性相当适合分页。关于分页，技术社区和论坛里讨论的非常多，下面就再简单说说利用mysql的limit的特性按照主键ID逆序分页取记录的功能实现。常见的分页处理方式是通过如下这种分页存储过程：

getpagerperson1-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPagerPersons`(
 CurrentPage int,  /* 当前页 */
 RecordCount int  /* 每页记录数 */
)
BEGIN

 IF RecordCount<=1 THEN 
  SET RecordCount=20;
 END IF;
 IF CurrentPage < 1 THEN 
  SET CurrentPage = 1; 
 END IF;

  /* 按条件分页查询 */
 SET @strsql = CONCAT('SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark FROM test.Person ORDER BY Id DESC',' LIMIT '
 ,CurrentPage*RecordCount-RecordCount,',',RecordCount); 
 prepare stmtsql from @strsql; 
 execute stmtsql; 
 deallocate prepare stmtsql;
 
 /* 查询总记录数 */
 SET @strsqlcount=CONCAT('SELECT COUNT(Id) AS TotalCount FROM test.Person');  
 prepare stmtsqlcount from @strsqlcount; 
 execute stmtsqlcount; 
 deallocate prepare stmtsqlcount; 
 
END

我们稍微分析一下就可以发现，对于数据量较大的查询，越往后面效率应该越低，因为越往后检索的记录行越多，这一点在高效的MySql分页介绍里已经得到验证。

和传入当前页码和每页记录数类似的是传入开始记录和结束记录这种方式，本质上和上面是没有区别的：

getpagerperson2-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPagerPersons1`(
 BeginRecordNumber int,  /* 开始记录 */
 EndRecordNumber int  /* 结束记录 */
 /* 
 如每页记录为10条，则
 第一页：BeginRecordNumber：1 EndRecordNumber：10
 第二页：BeginRecordNumber：11 EndRecordNumber：20
 */
 
)
BEGIN

  /* 按条件分页查询 */
 SET @strsql = CONCAT('SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark FROM test.Person ORDER BY Id DESC',' LIMIT '
 ,BeginRecordNumber-1,',',EndRecordNumber-BeginRecordNumber+1); 
 prepare stmtsql from @strsql; 
 execute stmtsql; 
 deallocate prepare stmtsql;
 
 /* 查询总记录数 */
 SET @strsqlcount=CONCAT('SELECT COUNT(Id) AS TotalCount FROM test.Person');  
 prepare stmtsqlcount from @strsqlcount; 
 execute stmtsqlcount; 
 deallocate prepare stmtsqlcount; 
 
END

真正可以高效的查询，当然是记住上一次查询结果的最小（或者最大）主键，这取决于查询是逆序还是升序排序。像下面这样实现的查询越往后翻页则可以省却查询很多记录行：

quickpagergetpersons-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPagerPersons`(
 CurrentPage int,  /* 当前页 */
 RecordCount int,  /* 每页记录数 */
 MinId int  /*前CurrentPage-1页的最小id，CurrentPage>1*/
)
BEGIN

 IF RecordCount<=1 THEN 
  SET RecordCount=20;
 END IF;
 IF CurrentPage < 1 THEN 
  SET CurrentPage = 1; 
 END IF;

-- 第一页
IF CurrentPage=1 THEN
 SET @strsql = CONCAT('SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark FROM test.Person ORDER BY Id DESC LIMIT ',RecordCount); 
END IF;

-- 第n页(n>1)
IF CurrentPage>1 THEN
 SET @strsql = CONCAT('SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark FROM test.Person WHERE Id<',MinId,' ORDER BY Id DESC LIMIT ',RecordCount); 
END IF;

 prepare stmtsql from @strsql; 
 execute stmtsql; 
 deallocate prepare stmtsql;
 
 /* 查询总记录数数 */
 SET @strsqlcount=CONCAT('SELECT COUNT(Id) AS TotalCount FROM test.Person');  
 prepare stmtsqlcount from @strsqlcount; 
 execute stmtsqlcount; 
 deallocate prepare stmtsqlcount; 
 
END

也就是说对于>1以后的查询都需要记住上次查询的一个“临界值”（通常情况下：如按Id逆序，最小Id；按照Id降序，最大Id）。园子里流传甚广的一篇博客高效的MySql分页讲的也是这个原理。记得早就有很多人总结过sql server下的分页方式，其中经典的二次top我感觉和limit的方式非常相似，只不过limit的效率可能更高一些。

注：在存储过程中LIMIT后面只能是常量而不能是变量，所以只好用字符串拼接生成SQL语句，然后动态执行即可。

【UPDATE】：经同事提醒，是MySql版本问题，本地的测试环境是MySql5.0，换成MySql5.5，MySql引擎已支持LIMIT后面使用变量。
同时还要切记，分页查询尽量按照主键正序或者逆序查询，否则按其他有可能重复的字段分页，查询的结果可能不准确（想想为什么？），实际开发经验中这一点已经得到了验证。

最后不能免俗，重新发布一个mysql通用分页存储过程，类似于sql server下的通用分页存储过程，核心思想当然是动态拼接字符串：

sp_pager-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Pager`(
 in CurrentPage int, /*当前页*/
 in RecordCount int, /*每页记录数*/
 in Columes varchar(1000), /*查询列*/
 in SqlWhere varchar(1000), /*查询条件*/
 in SqlOrder varchar(1000) /*排序*/
)
    COMMENT '通用分页存储过程'
BEGIN
 if RecordCount<=1 then 
  set RecordCount=20;
 end if;
 if CurrentPage < 1 then 
  set CurrentPage = 1; 
 end if;
 
  /* 按条件分页查询 */
 set @strsql = CONCAT('SELECT ',Columes,' FROM ',SqlWhere,'',SqlOrder,' LIMIT ',CurrentPage*RecordCount-RecordCount,',',RecordCount); 
 prepare stmtsql from @strsql; 
 execute stmtsql; 
 deallocate prepare stmtsql;
 
 /* 查询总记录数 */
 set @strsqlcount=CONCAT('SELECT COUNT(1) AS TotalCount FROM ',SqlWhere); /*count(1) 这个字段最好是主键*/
 prepare stmtsqlcount from @strsqlcount; 
 execute stmtsqlcount; 
 deallocate prepare stmtsqlcount; 
 
END

调用方式如下：

CALL test.sp_Pager(2,10,'Id,FirstName,LastName,CreateDate,UpdateDate,State','test.Person','ORDER BY ID DESC');

3、修改

通常情况下，按照主键修改一条记录信息相对而言比较简单，只要sql正确、传参无误，功能实现基本和sql server一模一样。

有时候我们需要进行跨表多数据量更新，比如sql server下可以通过update from的方式进行两张表或多张表数据更新，在mysql下如何实现update from方式的更新呢？

举例：有两张表Person和People，表结构完全一样。现在要实现sql server如下的功能：

updatefromUPDATE People 
SET Age=p1.Age+1
FROM Person	p1
WHERE p1.Id	=People.Id

对于sql server再简单不过的功能，mysql下需要稍微绕点弯，

mysqlupdatefromupdate test.People p join test.Person p1 
on p.Id = p1.Id 
set p.Age=p1.Age+1
where p.Id = p1.Id;

虽然看上去也还算简单，可是毕竟需要join查询。而对于数据量较大的表，我曾经得到过的一个重大教训就是减少连接查询。

关于修改的功能，还有一个地方需要注意，就是在原来对应列的数据基础上更新数据，比如将所有人的Age都更新为原来的数据加1,备注修改为全名：

UPDATE test.Person SET Age=Age+1,Remark=CONCAT(FirstName,LastName);

上面的sql语句执行是没有问题的。除非需要更新的列指定不明，执行的时候，sql编辑器会给出警告： Column '***' in field list is ambiguous。这就说明某一列指代不明。

4、删除

删除的功能和sql server非常相似，一条一条删除（DELETE FROM *** WHERE ***）无疑是最基础最常用的，这里不做示例展示。

和删除相关的就是清空表数据，sql server下的TRUNCATE TABLE *** 对mysql同样适用。

下面着重介绍删除重复记录的实现。

在sql server下，我们需要删除某列的重复记录（示例是FirstName相同，保留的列是ID最小的记录）很简单：

deleteduplicate     DELETE FROM Person WHERE Id NOT IN 
     (SELECT MIN(Id) AS MinId FROM Person GROUP BY FirstName )

ps：要找出表中某个字段的重复值，记得曾经在这篇随笔里提及过。原理就是count+分组：
　select 字段名,count(字段名) from 表名 group by 字段名 having count(字段名)>1

如果将上面的sql语句放在mysql下面执行，会给出错误警告：Error Code: 1093. You can't specify target table 'Person' for update in FROM clause。

同样的功能，在mysql里的实现如下：

mysqldeleteduplicateDELETE FROM test.Person WHERE Id NOT IN 
(SELECT MinId FROM (SELECT MIN(Id) AS MinId FROM test.Person GROUP BY FirstName) AS tmp);

感觉mysql的这种写法比较迂回一点，好坏不做评价。

三、需要注意的几点

1、插入的记录不合法

在插入的时候，比如插入FirstName的值超过了varchar（16）的长度，则抛出mysqlexception，并提示：Data too long for column 'FirstName' at row 1。这点类似于sql server下的二进制字节流截断的异常。

2、mysql语句的参数传递

经测试，在mysql的存储过程中传递一个和列名Id完全一样的叫Id的参数是相当危险的。在我的测试中，删除存储过程DeletePerson如下：

DeletePersonCREATE DEFINER=`root`@`localhost` PROCEDURE `DeletePerson`(
Id int
)
BEGIN
DELETE FROM Person WHERE Id=Id;
END

获取一条的存储过程如下：

GetPersonByIDCREATE DEFINER=`root`@`localhost` PROCEDURE `GetPersonByID`(
Id int
)
BEGIN

SELECT Id,FirstName,LastName,Age,CreateDate,UpdateDate,State,Remark
FROM test.Person
WHERE Id=Id;

END

更新一条记录的存储过程如下：

updatepersonCREATE DEFINER=`root`@`localhost` PROCEDURE `UpdatePerson`(
Id int,
FirstName varchar(16),
LastName varchar(16),
Age int,
CreateDate datetime,
UpdateDate datetime,
State int,
Remark varchar(256)
)
BEGIN

UPDATE test.Person SET
FirstName=FirstName,
LastName=LastName,
Age=Age,
CreateDate=CreateDate,
UpdateDate=UpdateDate,
State=State,
Remark=Remark
WHERE Id=Id;

END

但是执行的效果远不是期望的那样，真正的效果依次是删除所有记录、选取出所有记录和更新所有记录。这样很容易造成误删除或者误读取或者错误更新。解决的方法相当简单，给参数起个不是Id的别名即可。

那么通过sql语句而不是存储过程实现增删改查的参数传递如何呢？

我们不妨以update方法一试：

updateperson     var id = 1;
            string sql = "UPDATE test.Person SET Age=Age,UpdateDate=NOW() WHERE Id=Id;";
            var paramKeys = new string[] { "Age", "Id" };
            var paramVals = new object[] { 25, id };
            //这样写同样更新了所有记录,但是Age字段并没有更新
            var affectNum = MySqlHelper.ExecuteNonQuery(strSqlConn, sql, CommandType.Text, MySqlHelper.PrepareParameters(paramKeys, paramVals));
            Console.WriteLine("update affect rows:{0}", affectNum);

            //sql = "UPDATE test.Person SET Age=v_Age,UpdateDate=NOW() WHERE Id=v_Id;";
            //paramKeys = new string[] { "v_Age", "v_Id" };
            //paramVals = new object[] { 25, id };
            ////这样写抛出异常:Unknown column 'v_Id' in 'where clause'
            //affectNum = MySqlHelper.ExecuteNonQuery(strSqlConn, sql, CommandType.Text, MySqlHelper.PrepareParameters(paramKeys, paramVals));
            //Console.WriteLine("update affect rows:{0}", affectNum);

            sql = "UPDATE test.Person SET Age=@v_Age,UpdateDate=NOW() WHERE Id=@v_Id;";
            paramKeys = new string[] { "v_Age", "v_Id" };
            paramVals = new object[] { 25, id };
            //这样写更新一条记录成功
            affectNum = MySqlHelper.ExecuteNonQuery(strSqlConn, sql, CommandType.Text, MySqlHelper.PrepareParameters(paramKeys, paramVals));
            Console.WriteLine("update affect rows:{0}", affectNum);

            sql = "UPDATE test.Person SET Age=Age+1,UpdateDate=NOW() WHERE Id=@v_Id;";
            paramKeys = new string[] { "v_Id" };
            paramVals = new object[] { id };
            //这样写更新一条记录也是成功的
            affectNum = MySqlHelper.ExecuteNonQuery(strSqlConn, sql, CommandType.Text, MySqlHelper.PrepareParameters(paramKeys, paramVals));
            Console.WriteLine("update affect rows:{0}", affectNum);

            Console.Read();

实际效果果然非常诡异。

这里我大胆推测，官方提供的MySql 的C#客户端沿袭了Sql Server下的某些写法，所以，直接执行sql语句的时候，建议传递的参数必须带上“@”符号（经本地测试，增删改查都可以这么写；虽然@在存储过程里是个敏感字符，但是c#程序里调用存储过程传参也是可以使用@符号的），这个和前面拼接sql批量插入的地方有某种程度的巧合，而实际上人家可能就是这么设计传参的。

PS：我的一个牛人同事告诉我mysql存储过程传参的一些基本用法，其中着重强调的就是参数传递尽量按照mysql的标准来写，而不要沿袭sql server的写法，否则可能会造成不小困扰。他有非常丰富的开发经历，虽为经验之谈，经测试验证果然非常正确，今后必须汲取。

3、mysql的特殊语法和函数

我们知道，主流关系型数据库有多种，它们都有各自的特点和适用环境。所以如果一个人哪怕是多么了不起的dba，他也不太可能轻易地将各个平台的数据库知识都了然如胸。从这个层面来讲，学习通用而基础的sql原理和知识显得尤为重要，CSDN和博客园有很多优秀文章值得细细品读和学习。

对于像我一样的新手，我认为学好基础的sql，再深入熟练使用不同厂商的特殊语法和内置函数方能游刃有余，mysql的特殊语法和函数当然是一个重要的补充。这几天学到了不少mysql的“独特”写法，这里说它独特，主要还是先入为主，毕竟我个人使用sql server的开发经验远远高于mysql。准备将mysql和sql server的一些常见用法做个对比，这里不再一一列举。

四、打造自己的简易ORM

在之前介绍ado.net的几篇文章中，自己动手实现了一些类似orm的帮助类库（曾经改进了几次，但是不太令人满意），针对sql server的实现相对比较简单，而对mysql和oracle的一直没有动手。在文章最后的下载demo中，我参考了sql server的风格封又重新实现封装了个MySqlHelper（不是官方的那个MySqlHelper），感觉还不错。精力有限，有时间我可能也会把mysql的功能和sqlserver的实现整合在一起，当然还是等用熟了MySql再动手。
最后，求推荐一款简单易用的针对MySQL的ORM。

demo下载：demo

参考：

http://dev.mysql.com/doc/refman/5.6/en/connector-net.html

MySQL 5.1参考手册

http://kb.cnblogs.com/page/86245/

MySQL索引背后的数据结构及算法原理

SqlBulkCopy

O’REILLY <<SqlCookBook>>

posted on 2011-09-13 09:17 JeffWong 阅读(10228) 评论(5) 编辑收藏举报