使用mysql innodb 使用5.7的json类型遇到的坑和解决办法

#查询JSON的某个字段
select data -> ‘$.Host‘ from temp

#创建虚拟列
ALTER TABLE temp ADD host varchar(128) GENERATED ALWAYS AS (json_extract(data,‘$.Host‘)) VIRTUAL;

#给虚拟列创建索引
ALTER TABLE temp ADD INDEX index_temp_host ( host)

#用建立的第二索引列解决select count 的效率问题 

   select count(0) from temp where host>=0 ;

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放执行后的数据集合
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            adapter.Fill(dataset);
            cmd.Dispose();
            con.Close();
            return dataset.Tables[0];
        }