Category Archives: MySQL
MySQL查询数据时自动生成序号
在某些特定条件下，从数据库中查寻List数据的时候，需要给每组数据带上一个key序号。具体实现代码如下：
|
1 2 3 4 5 6 7 |
SELECT (@i:= @i+1) as 'key', c.country_name as countryName, c.country_code as countryCode FROM country c, (SELECT @i:=0) as i |
from:https://blog.csdn.net/weixin_41848005/article/details/123867009
View Details
mysql表名忽略大小写问题记录
为了避免大小写引发的问题，一种推荐的命名规则是：在定义数据库、表、列的时候全部采用小写字母加下划线的方式，不使用任何大写字母。
在任何系统中可以使用lower_case_tables_name=1。使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时，看不出名字原来是用大写还是小写。
请注意在Unix中如果以前lower_case_tables_name = 0将lower_case_tables_name设置为1之前，重启mysqld之前，必须先将旧的数据库名和表名转换为小写。
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
|
1 2 3 4 5 |
DETERMINISTIC 不确定的 NO SQL 没有SQl语句，当然也不会修改数据 READS SQL DATA 只是读取数据，当然也不会修改数据 MODIFIES SQL DATA 要修改数据 CONTAINS SQL 包含了SQL语句 |
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。 在MySQL中创建函数时出现这种错误的解决方法：
|
1 |
set global log_bin_trust_function_creators=TRUE; |
from:https://blog.csdn.net/zhangtxsir/article/details/105221057
View Details
Allow Zero Datetime=True产生的问题
MySQL连接字符串里加上了“Allow Zero Datetime=True”，导致错误：
|
1 |
System.Data.DataException: Error parsing column 15 (ADDDATE= - Object) ---> System.InvalidCastException: 指定的转换无效。 |
纠基原因，这句配置允许零日期后，MySQL与.Net的日期类型是不一样的，导致转换失败。去掉就正常了，默认值是False。
|
1 |
MySql.Data.Types.MySqlDateTime -> System.DateTime |
View Details
MYSQL设置触发器权限问题的解决方法
本文实例讲述了MYSQL设置触发器权限的方法，针对权限错误的情况非常实用。具体分析如下： mysql导入数据提示没有SUPER Privilege权限处理，如下所示：
|
1 |
ERROR 1419 (HY000): You do not have the SUPER Privilege and Binary Logging is Enabled |
导入function 、 trigger 到 MySQL database,报错:
|
1 |
You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”. |
原因： function / trigger 中有dangerous statements修改数据库，错误只在启用binary logging选项进行主从复制的服务器上出现。 解决方法如下： 1）导入数据的用户不仅需要CREATE ROUTINE, ALTER ROUTINE, CREATE TRIGGER, ALTER TRIGGER, CREATE FUNCTION 和 ALTER FUNCTION 权限，还需要SUPER privileges 权限，使用超级用户导入数据。 2）让所有用户具有执行类似functions的权限，危险，不推荐，
|
1 2 3 |
o by specifying it on the server start, like: –log-bin-trust-function-creators=1 o by setting it to 1 through the SET GLOBAL statement, like: mysql> SET GLOBAL log_bin_trust_function_creators = 1; |
3）如果不需要复制，或者是从库，关闭binlog，
|
1 2 3 4 |
# binary logging – not required for slaves, but recommended #log-bin=mysql-bin # binary logging format – mixed recommended #binlog_format=mixed |
希望本文所述对大家的MySQL数据库设计有所帮助。 from:https://www.jb51.net/article/54635.htm
View Detailsc# + mysql防SQL注入方法
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
/// <summary> /// 替换SQL注入的危险字符 /// </summary> /// <param name="param"></param> /// <returns></returns> public static string ReplaceInjectString(string param) { if (string.IsNullOrEmpty(param)) return string.Empty; var result = new StringBuilder(param); return result .Replace("'", "'") .Replace(",", ",") .Replace(";", ";") .Replace("(", "(") .Replace("=", "=") .Replace("*", "*") .Replace("&", "&") .ToString(); } |
View Details
mysql插入数据后返回自增ID的方法（AUTO_INCREMENT）
mysql插入数据后返回自增ID的方法 mysql在插入一条数据后，如何能获得到这个自增id的值呢？即怎么获取设置为自增主键的id（AUTO_INCREMENT）?? 方法一：是使用last_insert_id
|
1 |
mysql> SELECT LAST_INSERT_ID(); |
产生的自增ID每次连接后保存在服务器中。 这意味着函数向一个给定客户端返回的值是该客户端产生对影响AUTO_INCREMENT列的最新语句第一个 AUTO_INCREMENT值的。 这个值不能被其它客户端影响，即使它们产生它们自己的 AUTO_INCREMENT值。 这个行为保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁或处理。 每次mysql_query操作在mysql服务器上可以理解为一次“原子”操作, 写操作常常需要锁表的， 是mysql应用服务器锁表不是我们的应用程序锁表。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
值得注意的是，如果你一次插入了多条记录，这个函数返回的是第一个记录的ID值。 因为LAST_INSERT_ID是基于Connection的，只要每个线程都使用独立的Connection对象， LAST_INSERT_ID函数将返回该Connection对AUTO_INCREMENT列最新的insert or update*作生成的第一个record的ID。 这个值不能被其它客户端（Connection）影响，保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁。 使用单INSERT语句插入多条记录, LAST_INSERT_ID返回一个列表。 LAST_INSERT_ID 是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID会改变。 |
方法二：是使用max(id) 使用last_insert_id是基于连接（connection）的，如果换一个窗口的时候调用则会一直返回0， 如果不是频繁的插入我们也可以使用这种方法来获取返回的id值
|
1 |
select max(id) from user; |
这个方法的缺点是不适合高并发。如果同时插入的时候返回的值可能不准确。 方法三：是创建一个存储过程，在存储过程中调用先插入再获取最大值的操作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DELIMITER $$ DROP PROCEDURE IF EXISTS `test` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(in name varchar(100),out oid int) BEGIN insert into user(loginname) values(name); select max(id) from user into oid; select oid; END $$ DELIMITER ; call test('gg',@id); |
方法四:使用@@identity
|
1 |
select @@IDENTITY |
@@identity是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，是系统定 义的全局变量。一般系统定义的全局变量都是以@@开头，用户自定义变量以@开头。比如有个表A，它的自增列是id，当向A表插入一行数据后，如果插入数据 后自增列的值自动增加至101，则通过select @@identity得到的值就是101。使用@@identity的前提是在进行insert操作后，执行select @@identity的时候连接没有关闭，否则得到的将是NULL值。 方法五:是使用getGeneratedKeys()
|
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 |
Connection conn = ; Serializable ret = null; PreparedStatement state = .; ResultSet rs=null; try { state.executeUpdate(); rs = state.getGeneratedKeys(); if (rs.next()) { ret = (Serializable) rs.getObject(1); } } catch (SQLException e) { } return ret; |
from:https://blog.csdn.net/zhuchunyan_aijia/article/details/93620357
View DetailsMysql中的force index和ignore index
前几天统计一个sql，是一个人提交了多少工单，顺便做了相关sql优化。数据大概2000多w。
|
1 2 3 |
select CustName,count(1) c from WorkOrder where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; |
为了实验最少受其他因素干扰，将生产库的200多w数据导出来，用测试服务器进行测试。 导出来的数据是一个堆表，没有主键，没有索引。
|
1 2 3 4 5 |
mysql> show index from WorkOrder; 查询index方法1 Empty set (0.00 sec) mysql> show keys from WorkOrder; 查询index方法2 Empty set (0.00 sec) |
1.堆表的情况 这时候就在这时候，用执行计划分析下语句。
|
1 2 3 4 5 6 7 8 |
mysql> explain select CustName,count(1) c from WorkOrder where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+ | 1 | SIMPLE | WorkOrder | ALL | NULL | NULL | NULL | NULL | 2528727 | Using where; Using temporary; Using filesort | +----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+ 1 row in set |
select_type的值为SIMPLE，表示简单的select查询，不使用union或子查询。 type的值为ALL，表示要对表进行表扫描。 possible_keys 表示能使用哪个索引找到行记录。 key 表示Mysql决定使用的索引（键）。 key_len 表示Mysql决定使用索引的长度。 ref 表示使用哪个列和key一起从表中选择行。 rows 表示Mysql认为它执行查询时必须检查的行数。 extra 表示查询的详情信息，用到where，临时表，排序。 执行下该语句三次，发现执行了16.30 sec、16.34 sec、16.24 sec。 2.有索引的情况 建了四个索引，分别以custname，CreateDate建两个单列索引，另外两个是联合索引，只是最左边列不一样。
|
1 2 3 4 |
alter table WorkOrder add index ix_name(custname) alter table WorkOrder add index ix_date(CreateDate) alter table WorkOrder add index ix_namedate(custname,CreateDate) alter table WorkOrder add index ix_datename(CreateDate,custname) |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show keys from WorkOrder; +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | WorkOrder | 1 | ix_name | 1 | CustName | A | 1264363 | NULL | NULL | YES | BTREE | | | | WorkOrder | 1 | ix_date | 1 | CreateDate | A | 2528727 | NULL | NULL | | BTREE | | | | WorkOrder | 1 | ix_namedate | 1 | CustName | A | 1264363 | NULL | NULL | YES | BTREE | | | | WorkOrder | 1 | ix_namedate | 2 | CreateDate | A | 2528727 | NULL | NULL | | BTREE | | | | WorkOrder | 1 | ix_datename | 1 | CreateDate | A | 2528727 | NULL | NULL | | BTREE | | | | WorkOrder | 1 | ix_datename | 2 | CustName | A | 2528727 | NULL | NULL | YES | BTREE | | | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.00 sec) |
之后，用执行计划分析下sql查询语句。
|
1 2 3 4 5 6 7 8 |
mysql> explain select CustName,count(1) c from WorkOrder where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | range | ix_name,ix_date,ix_namedate,ix_datename | ix_datename | 4 | NULL | 824372 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ 1 row in set (0.01 sec) |
从执行计划可以看出，Mysql从四个索引中选取了ix_datename这个索引，type为range表示索引范围扫描。rows的数量值是没堆表的1/3。 执行语句三次，时间是 8.64 sec、8.61sec、8.55 sec。 我建了三个索引，那么我想用下另外三个索引怎么办？ 这里可以用force index()，这个指令可以指定本次查询强制使用哪个索引，因为Mysql优化器的选择并不是最优的索引。
|
1 2 3 4 5 6 |
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_namedate) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | index | ix_name,ix_namedate,ix_datename | ix_namedate | 307 | NULL | 2528727 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+ |
选用另一个联合索引 ix_namedate，这次type变为index，可以这样理解，根据索引的顺序进行全表扫描，比ALL效率要高些，rows的值和堆表的值差不多。 执行语句三次，时间是 7.84 sec、7.92 sec、7.84 sec。
|
1 2 3 4 5 6 7 8 |
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_name) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+ | 1 | SIMPLE | WorkOrder | index | ix_name,ix_namedate,ix_datename | ix_name | 303 | NULL | 2528727 | Using where; Using temporary; Using filesort | +----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+ 1 row in set |
选用另一个联合索引 ix_name，这次type是index，可以这样理解，根据索引的顺序进行全表扫描，比ALL效率要高些，rows的值和堆表的值差不多。 执行语句三次，时间是 1 min 28.17 sec、1 min 27.64 sec、1 min 27.58 sec。
|
1 2 3 4 5 6 7 |
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_date) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | range | ix_name,ix_date,ix_namedate,ix_datename | ix_date | 4 | NULL | 921062 | Using index condition; Using MRR; Using temporary; Using filesort | +----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+ |
选用另一个联合索引 ix_date，这次type是range，表示索引范围扫描，rows的值是堆表的1/3多些 。 执行语句三次，时间是 9.55 sec、9.52 sec、9.39 sec。 假如我不想用索引了怎么办？ 可以使用ignore index()，这个指令可以强制Mysql在查询时，不使用某索引。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> explain select CustName,count(1) c from WorkOrder ignore index(ix_date) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | range | ix_name,ix_namedate,ix_datename | ix_datename | 4 | NULL | 824372 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ mysql> explain select CustName,count(1) c from WorkOrder ignore index(ix_date,ix_name,ix_namedate,ix_datename) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+ | 1 | SIMPLE | WorkOrder | ALL | ix_name,ix_namedate,ix_datename | NULL | NULL | NULL | 2528727 | Using where; Using temporary; Using filesort | +----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+ |
上面第一个强制不使用ix_date索引，那么就Mysql就从剩下的三个索引中，选取他认为是最优的索引。第二个时将四个索引都不使用，那么Mysql就进行全表扫描了。 总结： 1.Mysql的语句优化，没有绝对的正确，explain也只是给出个大致的方向，例如 key_len值小的，rows小的按理说，时间应该最短，效率最高。但是，实验中时间最少的却不是那个值最小的。 2. 优化还需根据实际数据情况，例如，假如我where选取的时间范围变化，或者说CustName的分布有些变化，可能跟刚才的实验，又会产生一定偏差。 3. 同样我还实验了，当给表加上主键时，整体的查询时间会缩短些。 […]
View DetailsMySql异常：尝试读取超出流末尾的内容
问题详细描述： 2020-09-04 11:28:19,576 [DefaultQuartzScheduler_Worker-1] DEBUG MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. —> MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered attempting to read the resultset. —> MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed. —> System.IO.EndOfStreamException: 尝试读取超出流末尾的内容。 在 MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count) 在 MySql.Data.MySqlClient.MySqlStream.LoadPacket() 在 MySql.Data.MySqlClient.MySqlStream.LoadPacket() 在 MySql.Data.MySqlClient.MySqlStream.ReadPacket() 在 MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) 在 MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) 在 MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) 在 MySql.Data.MySqlClient.MySqlDataReader.NextResult() 在 MySql.Data.MySqlClient.MySqlDataReader.NextResult() 在 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) 在 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) 在 MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 在 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) 在 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] […]
View DetailsMYSQL连接字符串参数解析（解释）
被迫转到MySQL数据库，发现读取数据库时，tinyint类型的值都被转化为boolean了，这样大于1的值都丢失，变成true了。查阅资料MySQL中无Boolean类型，都是存储为tinyint了，这也无妨，但是读回时不应该都变成boolean啊。网络检索没有解决方案，准备向官方报告bug，细想官方不应该有这样低级错误，先下载Connector/NET源码研究一下，发现其中有TreatTinyAsBoolean这个属性，检索百度无结果，google有几条英文的，看来大家都没有注意到这点，或者说很少用.Net玩 MySQL的。 这样将数据库连接字符串变为这样形式就一切正常了： Database=dbname;Data Source=192.168.1.1;Port=3306;User Id=root;Password=****;Charset=utf8;TreatTinyAsBoolean=false; 顺便把源码中涉及的所有属性都列出来： Server，host, data source, datasource, address, addr, network address: 数据库位置(以上任何关键字均可) Database，initial catalog：数据库名 Port： socket 端口，默认 3306 ConnectionProtocol，protocol： 连接协议，默认 Sockets PipeName，pipe： 连接管道，默认 MYSQL UseCompression，compress： 连接是否压缩，默认 false AllowBatch： 是否允许一次执行多条SQL语句，默认 true Logging： 是否启用日志，默认 false SharedMemoryName：内存共享的名称，默认 MYSQL UseOldSyntax，old syntax, oldsyntax：是否兼容旧版的语法，默认 false ConnectionTimeout，connection timeout：连接超时等待时间，默认15s DefaultCommandTimeout，command timeout：MySqlCommand 超时时间，默认 30s UserID, uid, username, user name, user：数据库登录帐号 Password，pwd： 登录密码 PersistSecurityInfo：是否保持敏感信息，默认 false Encrypt：已经用 SSL 替代了，默认 false CertificateFile：证书文件(.pfx)格式 CertificatePassword：证书的密码 CertificateStoreLocation：证书的存储位置 CertificateThumbprint：证书指纹 AllowZeroDateTime：日期时间能否为零，默认 false ConvertZeroDateTime：为零的日期时间是否转化为 DateTime.MinValue，默认 false UseUsageAdvisor, usage advisor：是否启用助手，会影响数据库性能，默认 false ProcedureCacheSize，procedure cache, procedurecache：同一时间能缓存几条存储过程，0为禁止，默认 25 UsePerformanceMonitor，userperfmon, perfmon：是否启用性能监视，默认 false IgnorePrepare： 是否忽略 Prepare() 调用，默认 true UseProcedureBodies,procedure […]
View Details