.NET 操作 MySql

MySql性能最好ORM

在很多情况下MySql存在联表查询性能低下缺陷（比如分页Count查询），而sqlsugar的导航查询正好弥补了这一点

Includes(it=>it.Item)是拆分算法，不会生成联表sql，也不会生成多余sql，弥补了mysql很多不足

目前只有sqlsugar 导航Includes是拆分算法 

MySql支持

DbType.MySql 除了支持MySql还支持下面这些数据库：

MySQL,OceanBase ,PolarDB,Tidb,

MariaDB, Percona Server, Amazon Aurora,

Azure Database for MySQL, Google Cloud SQL for MySQL,kunDB

TDSQL、GoldenDB 、Doris

注意：个别特殊的数据库需要禁用Nvarchar(标题4)

 SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
 {
       DbType = DbType.MySql,
       ConnectionString ="server=localhost;Database=SqlSugar4xTest;Uid=root;Pwd=haosql",
       IsAutoCloseConnection = true
 });
 //个别特殊的数据库需要禁用Nvarchar(标题4)

字符串格式 ： 

 server=localhost;Database=SqlSugar4xTest;Uid=root;Pwd=haosql;
 
 //其他配置
 Port=3305 指定端口号 不指定是默认
 Charset= 设置编码 
 AllowLoadLocalInfile=true 启用bulkcopy
 
 //有些服务器防火墙有问题需要加上 min pool size=1 避免认为是恶意请求

说明：

如果用到bulkCopy需要加： AllowLoadLocalInfile=true

远程服务器 : 加上 min pool size等于1在一些外部服务器兼容比较好

如果ORM连接不上数据库：
请使用原生写法测试：new   MySqlConnection("字符串").Open(); 原生写法通过ORM就能用

如果是偶发情况：那么就使用SqlSugarScope 单例模式保证线程安全或者排查线程问题

1、date/time 错误

Unable to convert MySQL date/time value to System.DateTime

 解决办法3选1：

 1、将该字段的缺省值设置为null，而不是0000-00-00/0000-00-00 00:00:00的情况；

 2、在链接MySQL的字符串中添加：Convert Zero Datetime=True 或者 Allow Zero Datetime=True两个属性；

 3、将该字段设置成字符串类型；

2、MySql 乱码或表情 emoji

 2.1 连接字符串配置

 CharSet=utf8mb4;

 2.2 ORM推荐写法

//较新版本正常写就行

//老版本要换成下面写法
db.Insertable(List<实体>).UseParameter().ExecuteCommand()//5.0.3.8-Preview及以上版本支持

//老版本bulkCopy 
db.Fastest<Order>().SetCharacterSet("utf8mb4").BulkCopy(list1x)

 2.3 数据库配置

修改database字符集

ALTER DATABASE 数据库名 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

修改table字符集

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改column字符集

ALTER TABLE 表名 CHANGE 字段名 字段名 该字段原来的数据类型 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3、MySql插入时间带毫秒

 5.1 版本要求

 5.2 用法

[SugarColumn(ColumnDataType = "DATETIME(3) ")]
public DateTime CreateTime { get; set; }

驱动的DataReader.GetDateTime()是不支持查出毫秒的，所以你要查毫秒代码需要这么处理：

Select(it=>new {
           CreateTime=it.CreateTime.ToString() //转成string就能查出带毫秒的时间
         });

 5.3  .net framework 4.5.2  以下版本用法

[SugarColumn(ColumnDataType = "DATETIME(3) ")]
public  string CreateTime { get; set; } //直接用string

4、MYSQL 禁用NVarchar

特殊服务器不支持 N'xx' 这种 Narchar插入

MySQL表情可能也不能使用N进行插入

 DbType = SqlSugar.DbType.MySql,
    ConnectionString = Config.ConnectionString,
    InitKeyType = InitKeyType.Attribute,
    IsAutoCloseConnection = true,
    MoreSettings=new ConnMoreSettings() {
         DisableNarvchar=true //这里设置为true
    },

5、 MySql用户自定义SQL变量

在连接字符串里面加上 Allow User Variables=True

6、tinyint(1)

不是bool不要用这个类型，驱动会解析成bool,会导致计算结果错误

老项目可以禁用tinyint转成bool

TreatTinyAsBoolean=false//字符串加上

7、MySql bulkcopy

出现错误：The used command is not allowed with this MySQL version

出现错误: Loading local data is disabled; this must be enabled on both the client and server sides

解决方案：

1、需要添加配置 AllowLoadLocalInfile=true

2、如果添加了配置还报这个错 去MYSQL数据库执行：SET GLOBAL local_infile=1

8、The given key错误

The given key '23111' was not present in the dictionary

1、检查连接字符串是否正确

2、如果版本MySql  

独立安装MySql.Data  8.0.29 或者安装SqlSugar到最新版本

9、忽略重复 Ignore Insert用法

 db.Insertable(insertObj).MySqlIgnore().ExecuteCommand();//5.1.4.59+

10、表和实体大小写不一致

有些用户区表名分大小写，实体不是小写的我们可以统一转换

 
 var db= SqlSugarClient(new ConnectionConfig()
            {
                DbType = SqlSugar.DbType.MySql,
                ConnectionString = Config.ConnectionString,
                IsAutoCloseConnection = true,
                ConfigureExternalServices=new ConfigureExternalServices {
                    EntityNameService = (type,entity)=>
                    {       
                        entity.DbTableName = entity.DbTableName?.ToLower();//转小写
                    },
                    EntityService = (type, column) =>
                    {
                        column.DbColumnName = column.DbColumnName?.ToLower();//转小写
                    } 
                }
            })

11、建库添加字符集和表引擎

  11.1 建库加字符集

升级：SqlSugar 5.1.4.127-preview27

需要字符串上要配置charset=utf8mb4

StaticConfig.CodeFirst_MySqlCollate="utf8mb4_0900_ai_ci";//较高版本支持
db.DbMaintenance.CreateDatabase();//创建库

//如果库已存在那么用脚本手动把库的编码改了就行了，表是根据库来建的

  11.2 表加引擎

版本要求:5.1.4.163-preview05+

 //扔在程序启动执行一次
 StaticConfig.CodeFirst_MySqlTableEngine = " InnoDB ";
 //当然也可以
 StaticConfig.CodeFirst_MySqlTableEngine = " InnoDB DEFAULT CHARSET=utf8mb4";

12、MySql性能优化

原生的MySql最重要的一个性能优化就是count

1、数据量30万以上分页如果要查询count尽量避免联表操作

优化方法：使用导航查询或者thenmapper

2、数据量200万以上 哪怕单表查询 count都会明显变慢

优化方法:  不返回count只查前100页 这样可以解决

3、减少联表 可以用导航查询 Includes填充

注意：这个性能以实际开发为准不同的Mysql有差异，比如云数据库可能魔改过，可以自已在mysql中进行测试

13、事务注意点

（1）MYSQL不支持创建表和删除表处理事务,原生事务也一样 

（2） MyISAM 存储引擎不支持事务 需要改成 InnoDB 

14、禁用char(36)转GUID

驱动默认是将char36当作GUID来使用，我们可以通过字符串来禁用

//连接字符串加上，如果没有效果可以百度一下，我这个没有测试
OldGuids=True

15、连接不了库所有情况

先用原生.net进行测证是不是可以连上

//在没有编码情况下ORM会加上charset=utf8;
new MySqlConnection("xxxx;charset=utf8;").Open();

情况1

错误1： parametric information is abnormal.

错误2： Packets out of order. Got

错误3:   Unsupported command(Com_RESET_CONNECTION)

错误4:   Packets out of order. Got

连接字符串加上 ;Pooling=false; 可以解决

出现原因：一些特殊服务器环境或者魔改的MySql

原理： 用Ado.net模拟错误

var conn=new MySqlConnection(字符串);
conn.Open();
conn.Close();
//同一个连接第二次Open就会出来，出现这种情况 Pooling=false 会close后清空这个连接池，
//所以在次Open就不会有影响了
conn.Open();

方案1：字符串上加 Pooling=false (自动释放模式)

方案2： using(var db=new sqlsugarclient(手动释放模式)){ 。。 } 保证同一个db只会close一次

情况2

错误1：Connection open error . 调用 SSPI 失败

错误2:   .net framework  用户部分连不上

方案1： 字符串不仅要加 SslMode=none，还要将CharSet值由utf8改为utf8mb4

方案2:   net framework用户默认mysql.data 

我们换成 DbType=DbType.MySqlConnector

并且NUGET安装下面3个DLL ( .net core自带就是mysqlconnector)

情况3

第一次连不上，Navicat连接后又可以连上

方案： https://www.donet5.com/ask/9/24958

错误4

ORM默认会在字符串上加 ;charset=utf8 

方案：换一个不报错的编码

错误5

给定的关键字不在字典中

方案：字符串错误，如果是.net framework  升mysql.data

 错误6

SSL Authentication Error

方案：字符串加上 SslMode=none;AllowPublicKeyRetrieval=True;