mysql 动态增加列，查找表中有多少列，具体什么列。 通过JSON生成mysql表 支持子JSON

 

 [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public void TestMethod1()
        {
            using (testEntities entity = new testEntities())
            {
                var tableName = "test2";
                var json = "{\"name\":\"hello\", \"age\":1, \"createtime\":\"2012-04-23T18:25:43.511Z\", object:{\"column1\":\"test\", \"column2\":\"test\"}}";
                var jsonEntity = JsonConvert.DeserializeObject<Newtonsoft.Json.Linq.JObject>(json);

                var tableList = entity.Database.SqlQuery<MysqlTableSchema>("show tables").ToListAsync();
                tableList.Wait();

                var isExistsTable = tableList.Result.Where(r => r.tables_in_test.ToLower() == tableName.ToLower()).Count() > 0;
                if (!isExistsTable)
                {
                    entity.Database.ExecuteSqlCommand("CREATE TABLE `" + tableName + "` (`id` INT NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`));");
                }

                CreateColumns(tableName, jsonEntity);
            }
        }

        private static void CreateColumns(string tableName, JObject jsonEntity, string columPrefix = "")
        {
            using (testEntities entity = new testEntities())
            {
                var columList = entity.Database.SqlQuery<MysqlColumSchema>("DESCRIBE " + tableName).ToListAsync();
                columList.Wait();
                foreach (var item in jsonEntity.Properties())
                {
                    var columName = string.IsNullOrEmpty(columPrefix) ? item.Name.ToLower() : columPrefix + "_" + item.Name.ToLower();
                    var isExists = columList.Result.Where(e => e.field.ToLower().Equals(columName)).ToList().Count() > 0;
                    if (!isExists)
                    {
                        var mysqlType = "";
                        var index = "";
                        switch (item.Value.Type)
                        {
                            case Newtonsoft.Json.Linq.JTokenType.Object:
                                CreateColumns(tableName, item.Value.ToObject<JObject>(), columPrefix = item.Name.ToLower());
                                continue;
                            case Newtonsoft.Json.Linq.JTokenType.Boolean:
                                mysqlType = "nvarchar(1000) ";
                                index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";
                                break;
                            case Newtonsoft.Json.Linq.JTokenType.Bytes:
                                break;
                            case Newtonsoft.Json.Linq.JTokenType.Comment:
                                mysqlType = "nvarchar(1000) ";
                                index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";

                                break;
                            case Newtonsoft.Json.Linq.JTokenType.Date:
                                mysqlType = "datetime  NULL DEFAULT CURRENT_TIMESTAMP";
                                index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";

                                break;
                            case Newtonsoft.Json.Linq.JTokenType.Float:
                                mysqlType = "decimal(6,2) NULL DEFAULT '0.00'";
                                index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";

                                break;
                            case Newtonsoft.Json.Linq.JTokenType.Guid:
                                mysqlType = "nvarchar(45) ";
                                index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";

                                break;
                            case Newtonsoft.Json.Linq.JTokenType.Integer:
                                mysqlType = "int(11) NULL";
                                index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";

                                break;
                            case Newtonsoft.Json.Linq.JTokenType.String:
                                mysqlType = "nvarchar(1000) ";
                                index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";

                                break;
                            case Newtonsoft.Json.Linq.JTokenType.Uri:
                                mysqlType = "nvarchar(1000) ";
                                index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";

                                break;
                            default:
                                mysqlType = "nvarchar(2000) ";
                                break;
                        }
                        entity.Database.ExecuteSqlCommand("alter table " + tableName + " add column " +columName+ " " + mysqlType + ";" + index);
                    }
                }
            }
        }

        public class MysqlTableSchema
        {
            public string tables_in_test { get; set; }
        }

        public class MysqlColumSchema
        {
            public string field { get; set; }
            public string type { get; set; }
            public string key { get; set; }
            public string Null { get; set; }
            public string Extra { get; set; }
        }


    }