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) {
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 .


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 】.

©2019-2020 Toolsou All rights reserved,
Python Garbage collection and memory leak hive Summary of processing methods for a large number of small files The difference between memory overflow and memory leak , Causes and Solutions Create data mysql Library process You don't know ——HarmonyOS stay Vue Use in Web WorkerSparkSQL Achieve partition overlay write msf Generate Trojan horse attack android mobile phone Linux Page replacement algorithm C Language implementation Django Personal blog building tutorial --- Time classified archiving