Use MySQL InnoDB to use the 5.7 JSON type to encounter pits and workarounds

----------------------------------------------

#查询JSON的某个字段select data, ' $ '. Host ' from temp# creates a virtual column alter TABLE temp ADD host varchar (GENERATED) always as (json_extract (data, ' $. Host ') VIRTUAL; #给虚拟列创建索引ALTER TABLE temp ADD INDEX index_temp_host (host)

Select COUNT (0) from temp where host>=0;

　　

-------------------------------------------------------

EntityFramework cannot use the mysql5.7 solution. "Mysql-entity:the Value for column ' IsPrimaryKey ' on table ' tabledetails ' is DBNull"

(Current test: EntityFramework cannot use JSON type in mysql5.7, ADO can use JSON type)

You can use EntityFramework to execute a piece of code in MySQL.

Use Adventureworks;set global optimizer_switch= ' Derived_merge=off '; set optimizer_switch= ' Derived_merge=off '; select @ @optimizer_switch; select @ @GLOBAL. optimizer_switch;

　　

public static DataTable executedatatable (String sql, params mysqlparameter[] parameters)        {            mysqlconnection con = new Mysqlconnection ("Server=localhost;user Id=root;password=aaa_123;port=4407;database=testjson");            Con. Open ();            Mysqlcommand cmd = new Mysqlcommand (sql, con);            foreach (mysqlparameter parameter in parameters)            {                cmd. Parameters.Add (parameter);            }            DataSet DataSet = new DataSet ();//dataset data collection after execution            mysqldataadapter adapter = new Mysqldataadapter (cmd);            Adapter. Fill (dataset);            Cmd. Dispose ();            Con. Close ();            return DataSet. Tables[0];        }

　　

-----------------------------------------------------

About MySQL InnoDB count (*) Slow solution

The InnoDB engine is statistically different from MyISAM, and the MyISAM has a built-in counter, so when using select COUNT (*) from table, the data can be fetched directly from the counter. The InnoDB must be scanned all the time in order to get the total number. To solve this problem initially, we need to do something different from MyISAM: 1. Use a second index (typically not using a primary key index), and add a where condition, such as: SELECT COUNT (*) from product wherecomp_id>=0;     Show index from product; ID PRIMARY Keycomp_id Index2. You can also use show status from product if you only need rough statistics; To get the approximate value of this method can be used in data paging! 3, the use of external counters, such as the establishment of a trigger to count or on the program to use the cache on a timed count, the flaw is that these methods will consume some additional resources!

Pits and workarounds for using the 5.7 JSON type with MySQL InnoDB