Preface

Use in projects Dapper take JSON Data stored in MySQL data base , Results found JSON Chinese code in data , Hereby record , Want to store JSON Children's shoes can help , If there are any mistakes in the text , Criticism and correction .

Dapper Obtain JSON Data garbled (MySQL)

Where is the final problem in order to elicit , Let's start over , First, we present the following test entities and tables in the database , as follows :
public class Test { public int Id { get; set; } public string Data { get; set;
} }

For the convenience of data operation , We pass the package 【Dapper.SimpleCRUD】 To operate , Insert and query data as follows :
static void Main(string[] args) {
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
DefaultTypeMap.MatchNamesWithUnderscores= true; using (var conn = new
MySqlConnection(@".....;charset=utf8mb4;SslMode=none;")) { var id = conn.Insert(
new Test() { Data = JsonConvert.SerializeObject(new { name = " Wang Peng " }) }); var
result = conn.Get<Test>(id); } Console.ReadKey(); }

It's all right , Next we will store JSON Change the column type of data to json, Then do the above , As a result, it will be found that the Chinese character of the acquired data will be garbled :

 

At first, I thought it was a bag 【Dapper.SimpleCRUD】 There was a problem mapping , So I use it. Dapper Make the following query , The above Chinese code will still appear :
var result = conn.QueryFirstOrDefault<Test>("select * from Test where Id = @id"
,new { id });
  So we can basically come to a conclusion : In the light of MySQL Types in json, if json Chinese in data , Then make use of Dapper Random code will appear during query . So how to solve this problem ?
So , When adding data , I will do it in Chinese URL Code , Decode when querying again , as follows :
var id = conn.Insert(new Test() { Data = JsonConvert.SerializeObject(new { name
= HttpUtility.UrlEncode(" Wang Peng ") }) });

Of course , The above solutions are based on the actual project business , Our project needs to get JSON Chinese in the data is then passed URL transmission , Get the stored name without requesting the interface . Here we know Dapper In the light of json Data type will lead to the problem of query Chinese code , But what is the root cause ? Above connection MySQL Drive , Is used Oracle Official driver and latest version , as follows :

We know through EF
Core operation MySQL data base , If we use official packages, there are a lot of problems , So most of them are packages 【Pomelo.EntityFrameworkCore.MySql】, Because it contains 【MySqlConnector】, In other words, it exists MySQL Drive connection for , So here we try to replace the above with this package 【MySql.Data】 package , It turns out that Chinese is no longer garbled .

summary

In summary , What we need to pay attention to
If the official driver package is used 【MySql.Data】, When configured MySQL The column type in is json Time , if json Chinese in data , Then make use of Dapper Chinese code will appear during query , Conversion required
, In the database connection string above, we set to utf8mb4, So in theory, it should have nothing to do with coding ,
【 Latest updates : And Dapper Irrelevant ,utf8 Will lead to confusion , Table encoding needs to be configured as utf8mb4 Talent 】.

Technology
©2019-2020 Toolsou All rights reserved,
java Comparing attribute values between two objects utilize Python handle Excel data ——xlrd,xlwt library Bidirectional linked list Why? Python Not a future oriented programming language ?Python【 Assignment statement 】 Special lecture , You can't just a=b ah ! Suggest mastering ! utilize Python handle Excel data ——pandas library see SQL-SERVER Data volume and occupied space of database and each table PID The algorithm finally figured out the principle , It was so simple web Two front-end practical games ( Attached source code ) Beginners learn Python Be sure to know what his basic algorithms are ? What is the function ?