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

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111

[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; }        }        }