目录

简要说明

代码实现

---

简要说明

有时候项目中，在部署过程中，单独用工具去创建数据库和表，会消耗很多人力和时间，也不利于后期程序迭代去增加数据表，

那可以在程序启动的时候，去判断数据库和表是否存在，如果不存在则创建

包括后续表需要增加字段的话，也可以用语句去新增字段-可自行研究下，此篇文章没展示

下面的静态类方法可以直接复制使用，数据库链接的字段就要替换成你自己的数据即可，表结构数据也是

代码实现

  /// <summary>/// 数据库初始化/// </summary>public static class DBCreate{static string server = "127.0.0.1"; // 替换为你的服务器地址static string user = "root";     // 替换为你的用户名static string password = "123qwe"; // 替换为你的密码static string databaseName = "pz240606c"; // 替换为你的数据库名称static string T_Alarm = @"CREATE TABLE `pz240606c`.`T_Alarm`  (`ID` varchar(32) NOT NULL,`AlarmTime` datetime NOT NULL,`AlarmCode` varchar(32) NULL,`AlarmType` varchar(255) NULL,`AlarmContent` varchar(2048) NULL,`HandleTime` datetime NOT NULL,`IsUpLoad` int NULL,`CreateTime` datetime NOT NULL,PRIMARY KEY (`ID` DESC));";static string T_BoxNG = @"CREATE TABLE `pz240606c`.`T_BoxNG`  (`ID` varchar(32) NOT NULL,`Time` datetime NOT NULL,`Type` int NULL,`NGNum` int NULL,`QRCode` varchar(1024) NULL,`CreateTime` datetime NOT NULL,`Remark` varchar(2048) NULL DEFAULT NULL,`WorkNum` varchar(255) NULL,`IsTest` int NULL,PRIMARY KEY (`ID`));";static string T_LittleBox = @"CREATE TABLE `pz240606c`.`T_LittleBox`  (`ID` varchar(32) NOT NULL,`PackingQRCode` varchar(255) NOT NULL,`QRCode` varchar(255) NOT NULL,`BoxCode` varchar(255) NULL,`BoxName` varchar(255) NULL,`BoxSpec` varchar(255) NULL,`BoxColor` varchar(255) NULL,`Time` datetime NOT NULL,`CreateTime` datetime NOT NULL,`WorkNum` varchar(255) NULL,`IsUpload` int NULL,`IsTest` int NULL,PRIMARY KEY (`ID`));";static string T_Packing = @"CREATE TABLE `pz240606c`.`T_Packing`  (`ID` varchar(32) NOT NULL,`PackingCode` varchar(255) NULL,`PackingName` varchar(255) NULL,`PackingSpec` varchar(255) NULL,`PackingColor` varchar(255) NULL,`QRCode` varchar(255) NOT NULL,`Time` datetime NOT NULL,`CreateTime` datetime NOT NULL,`WorkNum` varchar(255) NULL,`IsUpload` int NULL,`IsTest` int NULL,PRIMARY KEY (`ID`));";static string T_TimeSummary = @"CREATE TABLE `pz240606c`.`T_TimeSummary`  (`ID` varchar(32) NOT NULL,`Type` int NOT NULL,`BeginTime` datetime NOT NULL,`EndTime` datetime NOT NULL,`CreateTime` datetime NOT NULL,`Remark` varchar(2048) NULL DEFAULT NULL,`WorkNum` varchar(255) NULL,PRIMARY KEY (`ID`)) COMMENT = '时间汇总表';";static Dictionary<string, string> tables = new Dictionary<string, string>(){{ "T_Alarm",DBCreate.T_Alarm},{ "T_BoxNG",DBCreate.T_BoxNG},{ "T_LittleBox",DBCreate.T_LittleBox},{ "T_Packing",DBCreate.T_Packing},{ "T_TimeSummary",DBCreate.T_TimeSummary },};/// <summary>/// 初始化数据库/// </summary>public static BaseResult CreateDB(){try{using (var connection = new MySqlConnection($"Server={server};User Id={user};Password={password};Port=3306;")){try{connection.Open();LogOperate.Start("Connected to MariaDB server.");// 检查数据库是否存在if (!DatabaseExists(connection, databaseName)){CreateDatabase(connection, databaseName);LogOperate.Start($"Database '{databaseName}' created.");}else{LogOperate.Start($"Database '{databaseName}' already exists.");}// 切换到目标数据库connection.ChangeDatabase(databaseName);foreach (var tb in tables){// 检查表是否存在if (!TableExists(connection, tb.Key)){CreateTable(connection, tb.Value);LogOperate.Start($"Table '{tb.Key}' created.");}else{LogOperate.Start($"Table '{tb.Key}' already exists.");}}}catch (Exception ex){LogOperate.Start($"An error occurred: {ex.Message}");return new BaseResult(false, "初始化数据库失败，" + ex.Message);}}return BaseResult.Successed;}catch (Exception ex){return new BaseResult(false, "初始化数据库失败，" + ex.Message);}}/// <summary>/// 检查数据库是否存在/// </summary>/// <param name="connection"></param>/// <param name="databaseName"></param>/// <returns></returns>static bool DatabaseExists(MySqlConnection connection, string databaseName){string query = $"SHOW DATABASES LIKE '{databaseName}'";using (var command = new MySqlCommand(query, connection)){using (var reader = command.ExecuteReader()){return reader.HasRows;}}}/// <summary>/// 创建数据库/// </summary>/// <param name="connection"></param>/// <param name="databaseName"></param>static void CreateDatabase(MySqlConnection connection, string databaseName){string query = $"CREATE DATABASE IF NOT EXISTS  {databaseName}";using (var command = new MySqlCommand(query, connection)){command.ExecuteNonQuery();}}/// <summary>/// 检查表是否存在/// </summary>/// <param name="connection"></param>/// <param name="tableName"></param>/// <returns></returns>static bool TableExists(MySqlConnection connection, string tableName){string query = $"SHOW TABLES LIKE '{tableName}'";using (var command = new MySqlCommand(query, connection)){using (var reader = command.ExecuteReader()){return reader.HasRows;}}}/// <summary>/// 创建表/// </summary>/// <param name="connection"></param>/// <param name="sql"></param>static void CreateTable(MySqlConnection connection, string sql){string query = sql;using (var command = new MySqlCommand(query, connection)){command.ExecuteNonQuery();}}}