MySQL的.NET驱动BUG引起DataTable在load时出异常的临时解决方案

完整测试用例如下

drop table if exists student; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `IX_student` (`name`) ) ENGINE=InnoDb AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; drop table if exists stufav; CREATE TABLE `stufav` ( `studentId` int(11) NOT NULL, `favoriteId` int(11) NOT NULL, PRIMARY KEY (`studentId`,`favoriteId`) ) ENGINE=InnoDb DEFAULT CHARSET=utf8; insert into student (id,name) values(1,'张三'); insert into student (id,name) values(2,'李四'); insert into student (id,name) values(3,'王五'); insert into student (id,name) values(4,'赵六'); insert into stufav values(2,1); insert into stufav values(2,11); insert into stufav values(2,111); select * from student s left join stufav sf on sf.studentId=s.id 运行结果为 1;张三;NULL;NULL; 2;李四;2;1; 2;李四;2;11; 2;李四;2;111; 3;王五;NULL;NULL; 4;赵六;NULL;NULL; 里面的name是唯一键，查询结果有多个李四。 C#的测试代码如下 using System; using System.Collections.Generic; using System.Text; using MySql.Data.MySqlClient; using System.IO; using System.Data; using System.Diagnostics; using System.Data.SqlClient; using MySql.Data.MySqlClient; namespace ConsoleApplication1 { class Program { private static string connectionString = "server=localhost;database=MySql;user id=sysdba;password=1234;charset=gb2312;port=5258"; private static void TestDataTableLoadNull() { using (MySqlConnection conn = new MySqlConnection(connectionString)) { try { conn.Open(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from student s left join stufav sf on sf.studentId=s.id"; MySqlDataReader reader = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(reader); } catch (Exception ex) { throw (ex); } finally { conn.Close(); } } } static void Main(string[] args) { TestMySQL.TestDataTableLoadNullMySql(); } } }

异常为：

"未能启用约束。一行或多行中包含违反非空、唯一或外键约束的值。"

临时解决方案有2个

<!--@import url(scrollbar.css); -->

1 使用Adapter方式代替DataTable的load方法

                    MySql Command cmd = conn.CreateCommand();

                    cmd.CommandText = "select * from student s left join stufav sf on sf.studentId=s.id";

                    MySqlDataAdapter adapter = new MySql DataAdapter();

                    adapter.SelectCommand = cmd;

                    DataSet ds= new DataSet();

                    adapter.Fill(ds);

                    return ds.Tables[0];

 

 

2 在查询的sql外面包一个select *

select * from (select * from student s left join stufav sf on sf.studentId=s.id) as MySql Table";