Category Archives: MySQL

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的权限，危险，不推荐，

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，

# 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

龙生 24 Dec 2021

View Details

c# + mysql防SQL注入方法

/// <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();

}

龙生 05 Nov 2021

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应用服务器锁表不是我们的应用程序锁表。

值得注意的是，如果你一次插入了多条记录，这个函数返回的是第一个记录的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;

这个方法的缺点是不适合高并发。如果同时插入的时候返回的值可能不准确。方法三：是创建一个存储过程，在存储过程中调用先插入再获取最大值的操作

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()

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

龙生 04 Nov 2021

View Details

Mysql中的force index和ignore index

前几天统计一个sql，是一个人提交了多少工单，顺便做了相关sql优化。数据大概2000多w。

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数据导出来，用测试服务器进行测试。导出来的数据是一个堆表，没有主键，没有索引。

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.堆表的情况这时候就在这时候，用执行计划分析下语句。

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;

+----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+

+----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+

+----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+

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建两个单列索引，另外两个是联合索引，只是最左边列不一样。

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)

mysql> show keys from WorkOrder;

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| WorkOrder | 1 | ix_name | 1 | CustName | A | 1264363 | NULL | NULL | YES | BTREE | | |

| WorkOrder | 1 | ix_namedate | 1 | CustName | A | 1264363 | NULL | NULL | YES | BTREE | | |

| WorkOrder | 1 | ix_datename | 2 | CustName | A | 2528727 | NULL | NULL | YES | BTREE | | |

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

6 rows in set (0.00 sec)

之后，用执行计划分析下sql查询语句。

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;

+----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+

+----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+

+----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+

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优化器的选择并不是最优的索引。

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;

+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+

+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+

+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+

选用另一个联合索引 ix_namedate，这次type变为index，可以这样理解，根据索引的顺序进行全表扫描，比ALL效率要高些，rows的值和堆表的值差不多。执行语句三次，时间是 7.84 sec、7.92 sec、7.84 sec。

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;

+----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+

+----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+

+----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+

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。

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;

+----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+

+----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+

+----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+

选用另一个联合索引 ix_date，这次type是range，表示索引范围扫描，rows的值是堆表的1/3多些。执行语句三次，时间是 9.55 sec、9.52 sec、9.39 sec。假如我不想用索引了怎么办？可以使用ignore index()，这个指令可以强制Mysql在查询时，不使用某索引。

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;

+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+

+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+

+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+

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;

+----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+

+----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+

+----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+

上面第一个强制不使用ix_date索引，那么就Mysql就从剩下的三个索引中，选取他认为是最优的索引。第二个时将四个索引都不使用，那么Mysql就进行全表扫描了。　　总结：　　　　 1.Mysql的语句优化，没有绝对的正确，explain也只是给出个大致的方向，例如 key_len值小的，rows小的按理说，时间应该最短，效率最高。但是，实验中时间最少的却不是那个值最小的。　　　　 2. 优化还需根据实际数据情况，例如，假如我where选取的时间范围变化，或者说CustName的分布有些变化，可能跟刚才的实验，又会产生一定偏差。　　　　 3. 同样我还实验了，当给表加上主键时，整体的查询时间会缩短些。 […]

龙生 22 Oct 2021

View Details

MySql异常：尝试读取超出流末尾的内容

问题详细描述： 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[] […]

龙生 12 Oct 2021

View Details

MYSQL连接字符串参数解析（解释）

被迫转到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 […]

龙生 30 Sep 2021

View Details

MySQL：创建高性能的索引

一、索引基础定义索引，也叫做“键(Key)”，是存储引擎用于快速查找记录的一种数据结构。索引对于良好的性能非常关键，索引是对查询性能优化最有效的手段。索引类型 1.B-Tree索引当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。B-Tree通常意味着所有的值是按顺序存储的，并且每一个叶子节点到根的距离相同。 B-Tree索引的几个匹配原则： a.全值匹配：和索引中的所有列进行匹配。 b.匹配最左前缀：即索引的第一列。 c.匹配列前缀：即只匹配某一列的值的开头部分。 d.匹配范围值。 e.精确匹配某一列并范围匹配另外一列。 f.只访问索引的查询，即：覆盖索引。 B-Tree索引的几个限制(索引失效)： a.不是按照索引的最左列开始查找，则不能使用索引。 b.不能跳过索引中的列。 c.如果查询中有某个列的范围查询，则其右边所有列都无法使用索引。 2.哈希索引哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效。在MySQL中只有Memory引擎显式支持哈希索引。我们在这里只作了解。 3.R-Tree索引 R-Tree索引(空间数据索引)，可以用作地理数据存储。MySQL中目前仅MyISAM引擎支持。与B-Tree不同，此类型的索引无须前缀查询。必须使用MySQL的GIS相关函数来维护数据，但遗憾的是MySQL的GIS支持并不完善。 4.全文索引全文索引是一种特殊类型的索引，它查找的是文本中关键词，而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。二、索引的优点优点： 1.索引大大减少了服务器需要扫描的数据量。 2.索引可以帮助服务器避免排序和临时表。 3.索引可以将随机I/O变为顺序I/O。什么样的表需要使用索引？简单的说应该遵循以下3条准则： 1.非常小的表：全表扫描更高效。 2.中到大型表：索引非常有效。 3.特大型的表：创建和使用索引的代价非常高。如果表有大量插入和更新，更新索引将是很大的一个开销。对于特大表，建议使用表分区技术，分区后再使用索引。三、高性能的索引策略示例以【居民表：resident】为例，以下是此表的结构： 1.独立的列独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。反例： 2.前缀索引和索引选择性前缀索引：如果需要索引的字符列很长，这会让索引变得大且慢。通常可以只索引此列开始的部分字符。这样可以大大节约索引空间，从而提高索引效率。索引选择性：是指不重复的索引值和数据表的记录总数的比值。此值越高，索引效率越高。例如：唯一索引和主键的索引选择性是1，性能也是最好的。索引选择性是创建前缀索引依据。例子：给列RESIDENT_NAME加索引，varchar(500)显然太长了，我们用前缀做索引。先计算完整列的选择性：0.4867 再计算最接近的前缀选择性，可以看到15个字符后，20个字符也是0.4867，因此15个字符作为前缀是最合适的。最后创建索引： 3.多列索引很多人对多列索引的理解都不够。一个常见的错误是：为每个列都创建独立的索引；另一个是按照错误的顺序创建多列索引。反例：独立索引对多条件查询的性能提升是很小的，一般只能匹配到一个索引，效率肯定要大打折扣的。正例：创建一个多列索引，扫描的行数马上降了下来，快了十几倍，这还只是个简单的示例。 4.选择合适的列顺序最让人困惑的问题莫过于索引列的顺序，正确的顺序依赖于使用该索引的查询。也要考虑到排序和分组的需要。例子：是什么让我决定以SP_ID在前创建了上面的多列索引：SP_ID_GENDER_CODE ? 答案是：计算各列的选择性。 5.聚簇索引聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。主键就是一个典型的聚簇索引。 6.覆盖索引如果一个索引包含要查询的所有字段，不需要再去表里读取数据，这样的索引就叫做覆盖索引。覆盖索引能极大的提高查询性能。例子：把查询中需要的字段改为索引中的字段时，这样一个覆盖索引就形成了。索引也只匹配到多列索引，ref也变为了常数。Extra也显示了Using index。 7.用索引做排序可以用同一个索引既满足排序，又用于查询。这样的索引是最优的，历为我们日常工作中遇到查询一般都是要排序的。这里有个限制：如果查询涉及到多表联合，排序用的字段必须全部是第一个表的，才能使用索引做排序。例子： 8.冗余和重复索引重复索引：是指在相同的列上按照相同的顺序创建的相同类型的索引。避免出现这样的情况，发现要删除。冗余索引：是指一个或多个列同步出现在多个索引中，各索引的列数、顺序不同。冗余索引也应避免。但有时查询写的不合理，可能出现单独为优化某个查询出现的冗余索引。 P.S 创建冗余索引时可能影响其他索引的匹配，从而导致以前的查询性能降低。四、索引案例索引 index(a, b, […]

龙生 22 Aug 2021

View Details

如何查询mysql数据库大小

要想知道每个数据库的大小的话，步骤如下： 1、进入information_schema 数据库（存放了其他的数据库的信息） use information_schema; 2、查询所有数据的大小： select concat(round(sum(data_length/1024/1024),2),’MB') as data from tables; 3、查看指定数据库的大小：比如查看数据库home的大小 select concat(round(sum(data_length/1024/1024),2),’MB') as data from tables where table_schema=’home'; 4、查看指定数据库的某个表的大小比如查看数据库home中 members 表的大小 select concat(round(sum(data_length/1024/1024),2),’MB') as data from tables where table_schema=’home' and table_name=’members'; from:https://blog.csdn.net/weixin_34050389/article/details/89826749

龙生 18 Aug 2021

View Details

« Previous 1 2 3 … 24 Next »