C#学习笔记 - 关于MySql数据库操作处理

1. 数据查询

1.1 关于查找字段时不区分大小写的解决方案

mysql查询时默认不区分大小写，若要区分大小写需要要在查询语句上+ “binary”

//不区分大小写
SELECT ID FROM hospital WHERE Name=@Name
//区分大小写
SELECT ID FROM hospital WHERE binary Name=@Name

1.2 多字段拼接查询

使用concat_ws拼接

concat_ws('-',record_subitem.SubGenotypeTMmin1 ,record_subitem.SubGenotypeTMmax1) as RangeTM1

注意：多表查询的时候不能添加非目标查询表，否则会产生大量的冗余重复数据。

1.3数据备份恢复

通过Sql语句模仿mysql的备份和恢复功能，Sql语句备份数据恢复时不管数据是否一样，无法保持不同的数据库数据一致，因为涉及到主键时，将无法删除数据。而要删除相关数据，再重新添加数据时会存在数据无法保持一致的问题，最好的解决办法时跟mysql自带的本分还原保持一致操作。
基本操作顺序如下：

1. 配置表的属性，使数据库允许删除表；

            if (!Connect()) return false;

            _mySqlCommand.Parameters.Clear();
            _mySqlCommand.CommandText = @"/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;";
            _mySqlCommand.CommandText += @"/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;";
            _mySqlCommand.CommandText += @"/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;";
            _mySqlCommand.CommandText += @"/*!50503 SET NAMES utf8 */;";
            _mySqlCommand.CommandText += @"/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;";
            _mySqlCommand.CommandText += @"/*!40103 SET TIME_ZONE='+00:00' */;";
            _mySqlCommand.CommandText += @"/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;";
            _mySqlCommand.CommandText += @"/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;";
            _mySqlCommand.CommandText += @"/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;";
            _mySqlCommand.CommandText += @"/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;";

            return DoExecuteNonQuery();

2. 删除旧的数据表；

            _mySqlCommand.Parameters.Clear();
            _mySqlCommand.CommandText = @"drop table if exists " + tableName;

            return DoExecuteNonQuery();

3. 创建新的数据表；

            if (!Connect()) return false;      //连接数据库
            //判断用户信息是否为空
            _mySqlCommand.Parameters.Clear();    //清除旧参数

            _mySqlCommand.CommandText = @"CREATE TABLE if not exists item ( "
                                        + " `ID` int(11) NOT NULL AUTO_INCREMENT,"
                                        + " `SN` varchar(45) NOT NULL,"
                                        + " `ItemTypeID` int(11) NOT NULL COMMENT '项目大类',"
                                        + " `SubItemTypeID` int(11) NOT NULL COMMENT '项目小类',"
                                        + "  `ChineseName` varchar(45) NOT NULL,"
                                        + "  `EnglishName` varchar(45) NOT NULL,"
                                        + "  `IsEnabled` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否启用',"
                                        + "  `Notes` text,"
                                        + " PRIMARY KEY(ID),"
                                        + " UNIQUE KEY `Sn_UNIQUE` (`SN`),"
                                        + " UNIQUE KEY `ID_UNIQUE` (`ID`),"
                                        + " UNIQUE KEY `Name_Zh_UNIQUE` (`ChineseName`),"
                                        + " UNIQUE KEY `Name_En_UNIQUE` (`EnglishName`),"
                                        + " KEY `link_ItemTypeID_idx` (`ItemTypeID`),"
                                        + " KEY `item.ItemType_itemtypelist.ID_idx` (`ItemTypeID`),"
                                        + " KEY `item.SubItemTypeID -> subitemtypelist.ID_idx` (`SubItemTypeID`),"
                                        + " CONSTRAINT `item_ibfk_1` FOREIGN KEY (`ItemTypeID`) REFERENCES `itemtypelist` (`ID`) ON UPDATE CASCADE,"
                                        + " CONSTRAINT `item_ibfk_2` FOREIGN KEY (`SubItemTypeID`) REFERENCES `subitemtypelist` (`ID`) ON DELETE RESTRICT ON UPDATE CASCADE"
                                        + " ) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='项目表';";

            return DoExecuteNonQuery();

4. 导入数据；

            _mySqlCommand.Parameters.Clear();    //清除旧参数

            StringBuilder sql = new StringBuilder();
            sql.Append("Insert into item values ");

            for (int i = 0; i < xElements.Count(); i++)
            {
                sql.Append(string.Format(FormatInfo.Number, "('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}'),"
                                    , xElements.ElementAt(i).Element("ID").Value
                                    , xElements.ElementAt(i).Element("SN").Value
                                    , xElements.ElementAt(i).Element("ItemTypeID").Value
                                    , xElements.ElementAt(i).Element("SubItemTypeID").Value
                                    , xElements.ElementAt(i).Element("ChineseName").Value
                                    , xElements.ElementAt(i).Element("EnglishName").Value
                                    , xElements.ElementAt(i).Element("IsEnabled").Value
                                    , xElements.ElementAt(i).Element("Notes").Value));
            }

            _mySqlCommand.CommandText = sql.ToString().Trim(',');

            bool result = DoExecuteNonQuery();

            DisenabledDropMode();

5. 恢复数据库参数配置

            if (!Connect()) return false;

            _mySqlCommand.Parameters.Clear();
            _mySqlCommand.CommandText = @"/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;";
            _mySqlCommand.CommandText += @"/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;";
            _mySqlCommand.CommandText += @"/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;";
            _mySqlCommand.CommandText += @"/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;";
            _mySqlCommand.CommandText += @"/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;";
            _mySqlCommand.CommandText += @"/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;";
            _mySqlCommand.CommandText += @"/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;";
            _mySqlCommand.CommandText += @"/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;";


            return DoExecuteNonQuery();

1.4 查询主键

SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE` where constraint_name=@KeyName`在这里插入代码片`

2.数据库操作

2.1 表主键重新开始编号

表没有外键的情况下：

truncate table_name;

表有外键存在的情况下

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table1; 
SET FOREIGN_KEY_CHECKS = 1;

3.异常问题记录

3.1 关于关键字

1. MySql表进行建立时应避免使用关键之，如where，with,table,row,column,in,insert，若使用关键字当作表名或者字段名无法直接查找。
2. 若 要查找关键字名称的字段，需要加具体表明，如table1.row。