在EF中使用MySQL的方法及常见问题

有时需要在网上租用空间或数据库,Mysql成本低一些,所以想将sql server转成mysql……

注意:在安装Mysql时要选择文字集为utf8,否则将不能使用中文(当前也可以在创建数据库时使用utf8,不过我不知道在ef生成数据库时如何设置,希望高手指点)

一、在项目中引用mysql的EF包

通过NuGet包管理器安装:EntityFramework6.1.3、MySql.Data.Entity6.9.8

也可以用nuget的命令行加入:

Install-Package MySql.Data.Entity

二、新建相关类

1、新建 User 实体类

并定义实例的字段长度,不定义的话会出现Specified key was too long;max key length is 767 bytes 的错误,这是因为string 类型直接映射到mysql 中的话是longtext,而mysql 支持最大长度为767 bytes.

public class User
{
public int Id { get; set; }
[StringLength(30)]
public string UserName { get; set; }
[MaxLength(30)]
public string PassWord { get; set; } } 

2、新建 MyContext 类

并说明用MySql进行实现 [DbConfigurationType(typeof(MySqlEFConfiguration))]

[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class MyContext : DbContext
{
public MyContext()
: base("name=MyContext")//web.config中connectionstring的名字
{
}
public DbSet<User> Users { get; set; }
}

3、写测试代码

Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MyContext>());
var context = new MyContext();
//插入一行值
context.Users.Add(new User { UserName = "EF6MySQL" });
context.SaveChanges(); 

三、配置Web.config

在<connectionStrings>中加入以下代码:

<add name="MyContext" connectionString="Data Source=localhost;port=3306;Initial Catalog=MySQL_EF;user id=root;password=root;" providerName="MySql.Data.MySqlClient" />

完整的web.config如下:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory , EntityFramework" />
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d">
</provider>
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
<connectionStrings>
<add name="MyContext" connectionString="Data Source=localhost;port=3306;Initial Catalog=MySQL_EF;user id=root;password=root;" providerName="MySql.Data.MySqlClient" />
</connectionStrings>
</configuration> 

最后,运行程序,完成数据库自动创建

常见问题

•出现错误提示: Specified key was too long;max key length is 767 bytes
1)查看实体的字符串类型属性是否设置了长度

2)MyContext 类中是否声明为生成为mysql 数据类型的 [DbConfigurationType(typeof(MySqlEFConfiguration))]

•出现错误提示: Model compatibility cannot be checked because the database does not contain model metadata

删除已生成的数据库后重新运行程序

•出现错误提示:序列不包含任何匹配元素

检查一下:
例如:1.

public class Employee
{
[Key]
public int EmployeeId { get; set; }
public string Name { get; set; }
[ForeignKey("ManagerId")]
public Employee Manager { get; set; }
public int ManagerId { get; set; }
}[ForeignKey("ManagerId")] public Employee Manager { get; set; } public int ManagerId { get; set; }这个外键设置。 

2.[Column(TypeName="VARCHAR(254)")] public string ColumnName { get; set; } 这样的定义,改成: [MaxLength(254)] [Column(TypeName="VARCHAR")] public string ColumnName { get; set; }3.(以下代码未测试,因为我不是这样用的,在下篇文章中将进行测试)

modelBuilder.Entity<Category>()
.HasKey(c => c.IdCategory )
.HasOptional(p => p.Children)
.WithMany()
.HasForeignKey(c => c.ChildrenId);

改成:

modelBuilder.Entity<Category>()
.HasKey(c => c.IdCategory )
.HasMany(p => p.Children)
.WithOptional()
.HasForeignKey(c => c.ChildrenId);

.WithMany()换成.WithOptional()
以上所述是小编给大家介绍的在EF中使用MySQL的方法及常见问题的全部叙述,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

探讨:sql插入空,默认1900-01-01 00:00:00.000的解决方法详解

sql2005 若字段定义的类型为datetime,插入为''(空),那么会默认值为1900-01-01 00:00:00.000

解决方法查询的时候过滤下cast(nullif('','') as datetime)

复制代码 代码如下:
select cast('' as datetime)
    , cast(nullif('','') as datetime)
    , isnull(cast(nullif('','') as datetime),getdate())
/*
----------------------- ----------------------- -----------------------
1900-01-01 00:00:00.000 NULL                    2009-02-25 17:18:15.140

(1 行受影响)

*/

详解MySQL数据类型DECIMAL(N,M)中N和M分别表示的含义

同事问MySQL数据类型DECIMAL(N,M)中N和M分别表示什么含义,M不用说,显然是小数点后的小数位数,但这个N究竟是小数点之前的最大位数,还是加上小数部分后的最大位数?这个还真记不清了。于是乎,创建测试表验证了一番,结果如下:

测试表,seller_cost字段定义为decimal(14,2)

CREATE TABLE `test_decimal` (
 `id` int(11) NOT NULL,
 `seller_cost` decimal(14,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

起初,表中内容为空

mysql> select * from test_decimal;
Empty set (0.00 sec)

插入整数部分长度为14的数字,报超出列范围的错误

mysql> insert into test_decimal(id,seller_cost) values(1,12345678901234);
ERROR 1264 (22003): Out of range value for column 'seller_cost' at row 1

插入整数部分长度为12的数字,可以正确插入

mysql> insert into test_decimal(id,seller_cost) values(1,123456789012);
Query OK, 1 row affected (0.00 sec)

查询表,发现插入的整数值末尾被MySQL补了两位小数“.00”

mysql> select * from test_decimal;
+----+-----------------+
| id | seller_cost   |
+----+-----------------+
| 1 | 123456789012.00 |
+----+-----------------+
1 row in set (0.00 sec)

继续插入整数部分12位,小数部分5位的数字,可以成功插入,但是有警告,警告表明小数部分发生了截断,被截取成了两位小数

mysql> insert into test_decimal(id,seller_cost) values(1,123456789012.12345);
Query OK, 1 row affected, 1 warning (0.00 sec)
 
mysql> show warnings;
+-------+------+--------------------------------------------------+
| Level | Code | Message                     |
+-------+------+--------------------------------------------------+
| Note | 1265 | Data truncated for column 'seller_cost' at row 1 |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select * from test_decimal;
+----+-----------------+
| id | seller_cost   |
+----+-----------------+
| 1 | 123456789012.00 |
| 1 | 123456789012.12 |
+----+-----------------+
2 rows in set (0.00 sec)

缩小整数部分的长度为2,小数部分的长度继续保持为5,可以成功插入,但小数部分被截断为两位。

mysql> insert into test_decimal(id,seller_cost) values(1,12.12345);
Query OK, 1 row affected, 1 warning (0.00 sec)
 
mysql> show warnings;
+-------+------+--------------------------------------------------+
| Level | Code | Message                     |
+-------+------+--------------------------------------------------+
| Note | 1265 | Data truncated for column 'seller_cost' at row 1 |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select * from test_decimal;
+----+-----------------+
| id | seller_cost   |
+----+-----------------+
| 1 | 123456789012.00 |
| 1 | 123456789012.12 |
| 1 |      12.12 |
+----+-----------------+
3 rows in set (0.00 sec)

继续插入一个小数部分不足两位的数字,可正确插入,且小数部分被自动补全到两位。

mysql> insert into test_decimal(id,seller_cost) values(1,12.1);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from test_decimal;
+----+-----------------+
| id | seller_cost   |
+----+-----------------+
| 1 | 123456789012.00 |
| 1 | 123456789012.12 |
| 1 |      12.12 |
| 1 |      12.10 |
+----+-----------------+
4 rows in set (0.00 sec)

综上所述,DECIMAL(N,M)中M值的是小数部分的位数,若插入的值未指定小数部分或者小数部分不足M位则会自动补到M位小数,若插入的值小数部分超过了M为则会发生截断,截取前M位小数。N值得是整数部分加小数部分的总长度,也即插入的数字整数部分不能超过N-M位,否则不能成功插入,会报超出范围的错误。

总结

以上就是本文关于详解MySQL数据类型DECIMAL(N,M)中N和M分别表示的含义的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站:MySQL中or语句用法示例简述Redis和MySQL的区别等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对本站的支持!

mysql 存储过程中变量的定义与赋值操作

一、变量的定义
mysql中变量定义用declare来定义一局部变量,该变量的使用范围只能在begin...end 块中使用,变量必须定义在复合语句的开头,并且是在其它语句之前,也可以同时申明多个变量,如果需要,可以使用default赋默认值。
定义一个变量语法如下:
declare var_name[,...] type[default value]看一个变量定义实例
declare last date;二、mysql存储过程变量赋值
变量的赋值可直接赋值与查询赋值来操作,直接赋值可以用set来操作,可以是常量或表达式如果下

复制代码 代码如下:
set var_name= [,var_name expr]...给上面的last变量赋值方法如下
set last = date_sub( current_date(),interval 1 month);下面看通过查询给变量赋值,要求查询返回的结果必须为一行,具体操作如下
select col into var_name[,...] table_expr我们来通过查询给v_pay赋值。
create function get _cost(p_custid int,p_eff datetime)
return decimal(5,2)
deterministic
reads sql data
begin
declare v_pay decimail(5,2);
select ifnull( sum(pay.amount),0) into vpay from payment where pay.payd<=p_eff and pay.custid=pid
reutrn v_rent + v_over - v_pay;
end $$

好了,这篇简单的存储过程中变量的定义赋值教程就到这里了,下面我们会接着讲关于myql存储过程的条件的定义与处理。

以下是其它网友的补充

在MySQL的存储过程中,可以使用变量,它用于保存处理过程中的值。

定义变量使用DECLARE语句,语法格式如下:

DECLARE var_name[,...] type [DEFAULT value]

其中,var_name为变量名称,type为MySQL支持的任何数据类型,可选项[DEFAULT value]为变量指定默认值。一次可以定义多个同类型的变量,各变量名称之间以逗号“,”隔开。

定义与使用变量时需要注意以下几点:

◆ DECLARE语句必须用在DEGIN…END语句块中,并且必须出现在DEGIN…END语句块的最前面,即出现在其他语句之前。

◆ DECLARE定义的变量的作用范围仅限于DECLARE语句所在的DEGIN…END块内及嵌套在该块内的其他DEGIN…END块。

◆ 存储过程中的变量名不区分大小写。

定义后的变量采用SET语句进行赋值,语法格式如下:

SET var_name = expr [,var_name = expr] ...

其中,var_name为变量名,expr为值或者返回值的表达式,可以使任何MySQL支持的返回值的表达式。一次可以为多个变量赋值,多个“变量名=值”对之间以逗号“,”隔开。

例如:

复制代码 代码如下:
begin
declare no varchar(20);
declare title varchar(30);
set no='101010',title='存储过程中定义变量与赋值';
end

提示:存储过程中所有的关键字也是不区分大小写的,如BEGIN可以写出begin。

如何恢复Mysql数据库的详细介绍

由于在一台测试机器上打算重新安装Mysql数据库,由于简单粗暴的直接卸载了,没有备份公司Discuz和Redmine使用的Mysql数据库,过程可想的悲惨。

还好的是只是卸载掉了Mysql的程序,所有的数据文件还是存在的。

下面是在恢复数据库的过程

1. Discuz数据库

Discuz数据库的恢复非常顺利, 在安装好新版本的Mysql后,直接将原来的数据库文件copy到新的数据目录中,重新启动mysql, 就能看到恢复的数据库了

2. Redmine数据库

本打算直接使用上面的经验,也能看到所有的表,但是就是执行查询的时候,总是报错"表不存在".

后来查了一些资料,发现,原因应该是Discuz和Redmine使用的Mysql引擎不一样导致的。

Discuz使用的是MyISAM, 而Redmine使用的是InnoDB.

解决的办法是,

除了要copy数据目录外,还要记得覆盖ibdata1文件。

以表”Table”为例: 如类型是MyISAM, 数据文件则以”Table.frm””Table.MYD””Table.MYI””三个文件存储于”/data/$databasename/”目录中. 如类型是InnoDB, 数据文件则存储在”$innodb_data_home_dir/″中的ibdata1文件中(一般情况),结构文件存在于table_name.frm中. MySQL的数据库文件直接复制便可以使用,但是那是指“MyISAM”类型的表。 而使用MySQL-Front直接创建表,默认是“InnoDB”类型,这种类型的一个表在磁盘上只对应一个“*.frm”文件,不像MyISAM那样还“*.MYD,*.MYI”文件。 MyISAM类型的表直接拷到另一个数据库就可以直接使用,但是InnoDB类型的表却不行。解决方法就是:

同时拷贝innodb数据库表“*.frm”文件和innodb数据“ibdata1”文件到合适的位置。启动MySQL的Windows服务 由于MySQL这样数据混杂的形式, 往往很容易让使用者在备份时忘记了备份InnoDB, 从而导致了上述错误.

意思就是说在数据库引擎类型为InnoDB时,拷贝数据文件的同时还需要拷贝ibdata1,于是把ibdata1也拷贝过去覆盖,发现还是有点问题,于是停止mysql服务,将目录下的ib_logfile*文件全部删除掉,重新启动mysql服务,well done,可以了

高兴啊,于是稍微总结了,希望以后遇到相同的问题,能够快速解决。

1,在进行mysql数据库备份的或迁移的时候,尽量备份完成所需要的数据;

2,如果直接拷贝原有数据库文件"*.frm"、"*.MYD"、"*.MYI"等文件时候,如果原数据库引擎是InnoDB,切记还需拷贝ibdata1文件

3,备份数据库的时候,最好是用相关的工具进行备份或是导出sql文件,以免浪费时间在数据库恢复上

4,msyql版本或是备份工具的版本不同,也可能引起数据恢复有问题。

实践证明以上问题是存在的,解决方案是可行的,哈哈,为了以后方便,写了这篇博客随笔,希望大牛看到了不要鄙视,欢迎拍砖。

 1:MyISAM类型的数据文件可以在不同操作系统中COPY,这点很重要,布署的时候方便点。(只需要拷贝 数据库名字文件夹下面的文件,这样数据库就拷贝完了)

2:  InnoDB类型的 要注意多拷贝 ibdata1 , 最好不要是直接复制文件夹,而是应该用sql导入导出

逐步分析MySQL从库com

大家都知道com_insert等com_xxx参数可以用来监控数据库实例的访问量,也就是我们常说的QPS。并且基于MySQL的复制原理,所有主库执行的操作都会在从库重放一遍保证数据一致,那么主库的com_insert和从库的com_insert理论上应该是相等的。
如下面显示,第二列代表主库,第三列代表从库:

复制代码 代码如下:
com_select              22                 1138
com_update              36                   37
com_insert             133                  135
com_delete               0                    0
qcache_hits              0                    0
Com_replace              0                    0
Connections             13                   24

但是我们看另外一个业务:

复制代码 代码如下:
com_select               0                   95
com_update               0                    0
com_insert              92                    0
com_delete              20                    0
qcache_hits              0                    6
Com_replace              0                    0
Connections              0                    6

我们可以很明显的看出来,主库有92个写,但是从库0个写,这是为什么呢?

这2个业务唯一的区别就是binlog_format的设置不一样。

复制代码 代码如下:
第一个业务
show global variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

第二个业务
show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

我们来看下com_xxx的官方文档定义:

The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_update count DELETE and UPDATE statements, respectively. Com_delete_multi and Com_update_multi are similar but apply to DELETE and UPDATE statements that use multiple-table syntax.

从上述文档,我们只能看到com_xxx是如何运作的,但是并不能解释为什么使用RBR之后com_insert就不变化了。

接下来我们结合下面这段文档来一起看看。

You cannot examine the logs to see what statements were executed, nor can you see on the slave what statements were received from the master and executed.
However, you can see what data was changed using mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose.

这2段话结合来看,原因应该是这样的:

1、主库上接收的是statement的语句,所以com_insert符合触发条件,会随着业务增加。

2、而从库是拿到主库的binlog后重放更新数据,但是主库的日志格式是row format,这就导致了binlog中记录的不是statement语句,而是data的变化记录。

3、这样从库虽然依然能进行更新记录,但是无法解析出来这些data变化是一条statement语句导致的还是多条statment语句导致,所以就不在更新com_insert这个statment counter了。

基本上推论符合现实情况,但是没有code证明,比较遗憾。

另外,如果我们无法通过com_insert来监控从库的写入情况,那么我们应该监控那个status呢?

个人建议对于row格式的实例,通过监控innodb_rows_inserted来监控写入情况。

复制代码 代码如下:
show global status like 'innodb_rows_inserted';
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| Innodb_rows_inserted | 2666049650 |
+----------------------+------------+

附:(两个文档的官方文档链接)

http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Com_xxx

http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html

5个常用的MySQL数据库管理工具详细介绍

1、phpMyAdmin 
phpMyAdmin是最常用的MySQL维护工具,是一个用PHP开发的基于Web方式架构在网站主机上的MySQL管理工具,支持中文,管理数据库非常方便。不足之处在于对大数据库的备份和恢复不方便。 
 
2、MySQLDumper 
MySQLDumper使用PHP开发的MySQL数据库备份恢复程序,解决了使用PHP进行大数据库备份和恢复的问题,数百兆的数据库都可以方便的备份恢复,不用担心网速太慢导致中间中断的问题,非常方便易用。这个软件是德国人开发的,还没有中文语言包。 

 

3、Navicat 
Navicat是一个桌面版MySQL数据库管理和开发工具。和微软SQLServer的管理器很像,易学易用。Navicat使用图形化的用户界面,可以让用户使用和管理更为轻松。支持中文,有免费版本提供。 

 

4、MySQL GUI Tools 
MySQL GUI Tools是MySQL官方提供的图形化管理工具,功能很强大,值得推荐,可惜的是没有中文界面。 

 

5、MySQL ODBC Connector 
MySQL官方提供的ODBC接口程序,系统安装了这个程序之后,就可以通过ODBC来访问MySQL,这样就可以实现SQLServer、Access和MySQL之间的数据转换,还可以支持ASP访问MySQL数据库。

mysql 一次更新(update)多条记录的思路

工作中遇到一个问题:要更新一个数据表。

这个表是我自己创建的,有7个字段,id、name、package等等

创建的时候,因为我把name、package的信息分别存在两个文本文件中,

所以我就用Insert方法,一次性将所有的name插入数据库中。

name全部导入数据库中了,但是我的package没有导入,这时我仍然想用insert的方法插入,但是不行。

这时候应该利用update的方法。一次更新多条信息的思路如下:

UPDATE table_name 
SET field_name = CASE other_field 
WHEN 1 THEN 'value' 
WHEN 2 THEN 'value' 
WHEN 3 THEN 'value' 
END 
WHERE id IN (1,2,3)
测试代码如下:
<?php 
/* 
*function: insert app's apk ,logo_url, document_title,app_desc,package_name 
* into talbe atable use database db . 
*/ 

//connect database catx. 
$server='localhost'; 
$user='root'; 
$passwd='root'; 
$port='3306'; 
$dbname='catx'; 
$link=mysql_connect($server,$user,$passwd); 
if (!$link) { 
die('Could not connect: ' . mysql_error()); 
} 
else echo "Connected successfully\n"; 
mysql_select_db("db",$link); 
//set init variable and start time 
$st=microtime_float(); 
$table="pydot_g"; 
$path = "txt"; 
$fname_package_name = "package_name.txt"; 
// 
$handle= @fopen($path."/".$fname_package_name, "r"); 
$i=1; 
$sql = "UPDATE pydot_g SET package_name = CASE id "; 
$ids=""; 
while(($buf[$i]=fgets($handle,512))!==false){ 
$sql .= sprintf("WHEN %d THEN '%s' ", $i, $buf[$i]); // 拼接SQL语句 
$ids .= sprintf("%d,",$i); 
$i++; 
} 
//$ids=implode(',',$ids); 
$ids.=$i; 
$sql .= "END WHERE id IN ($ids)"; 
echo $sql; 
mysql_query($sql); 
fclose($handle); 
mysql_close($link); 
//echo the results and total time used 
$et=microtime_float(); 
$t=$et-$st; 
echo "\r\ninsert into talbe ",$table," ",$i,"times;\r\n"; 
echo "Total time $t seconds.\r\n"; 
//function calculate time ,return a float number 
function microtime_float(){ 
list($usec, $sec) = explode(" ", microtime()); 
return ((float)$usec + (float)$sec); 
} 
?>

MySQL 8.0.0开发里程碑版发布!

MySQL 开发团队于 12 日宣布 MySQL 8.0.0 开发里程碑版本(DMR)发布! 可能有人会惊奇 MySQL 为何从 5.x 一下跳跃到了 8.0。事实上,MySQL 5.x 系列已经延续了很多年,从被 Oracle 收购之前就是 5.1,而收购之后一直维持在 5.x,比如 5.5,5.6,5.7 等等。其实,如果按照原本的发布节奏,可以把 5.6.x 当成 6.x,5.7.x 当成 7.x。所以,只是换了版本命名方式而已。

  不过这次发布的 MySQL 8.0.0 开发版本还是有不少亮点的。

MySQL 8.0.0 亮点

事务性数据字典,完全脱离了 MyISAM 存储引擎

真正将数据字典放到了 InnoDB 中的一些表中,从此不再需要 FRM、TRG、PAR 文件啦!Information Schema 现在以数据字典表的一个视图出现。原则上可以完全不需要 MyISAM 数据表类型了,所有的系统表都可以放到 InnoDB 之中。

SQL 角色

角色是一系列权限的集合。可以创建角色,给某个用户授予和去除角色。这对于权限管理很方便。

utf8mb4 字符集将成为默认字符集,并支持 Unicode 9

默认字符集将从 latin1 改为 utf8mb4,默认定序 collation 将从 latin1_swedish_ci 改为 utf8mb4_800_ci_ai。

不可见索引

可以将一些索引设置为不可见,这样 SQL 优化器就不会用到它,但是它会继续在后台保持更新。当有需要时,可以随时恢复可见。

对二进制数据可以进行位操作

不仅仅可以对 BIGINT 进行位操作,从 8.0 开始也支持对 [VAR]BINARY/[TINYMEDIUMLONG]BLOB 进行位操作了。

改进了对 IPv6 和 UUID 的操作

INET6_ATON () 和 INET6_NTOA () 现在可以进行位操作了,因为 INET6_ATON ()现在返回的是 VARBINARY (16) 数据类型(128 位)。改进了 UUID 操作,引入了三个新的函数 UUID_TO_BIN (), BIN_TO_UUID ()和 IS_UUID () 。MySQL 并没有特殊的 IPv6 和 UUID 数据类型,而是以 VARBINARY (16) 数据类型保存的。

持续性的全局变量

可以用 SET PERSIST 来设置持久性的全局变量,即便重启也会保持下来。

性能数据库 Performance Schema 的改进

比如对性能数据库增加了 100 多个索引,可以检索更快。

重构 SQL 分析器

持续不断的逐步改进 SQL 分析器。旧的分析器由于其语法复杂性和自顶向下的分析方式从而有严重的限制,导致难以维护和扩展。

成本模型

InnoDB 缓冲区现在可以估算主内存缓存区中的有多少表和索引,这可以让优化器选择访问方式时知道数据是否可以存储在内存中还是必须存储到磁盘上。

直方图 Histograms

通过使用直方图,用户或 DBA 可以对数据分布进行统计,这可以用于查询优化以寻找优化的查询方案。

改进扫描性能

改进了 InnoDB 范围查询的性能,可提升全表查询和范围查询 5-20% 的性能。

重构 BLOB

重构 BLOB 加速了片段读取/更新操作,可以加速 JSON 数据的操作。

持久化自增值

InnoDB 会持久化保持自增序列的最大值到 redo 日志中。这个改进还修复了一个非常老的 199 号 bug。

临时表

取消对压缩临时表的支持,并存储临时表的元数据到内存中。

  其它的更多重要改进和细节,请参考 MySQL 8.0.0 发布公告[1]和[2]

下载

  目前 8.0.0 还是开发版本,如果你希望体验和测试最新特性,可以从 dev.mysql.com[3] 下载各个平台的安装包。不过,MySQL 软件包是越来越大了,Linux 平台上的二进制打包后就将近有 1 GB。如果在产品环境中使用,在 8.0 没有进入稳定版本之前,请继续使用 5.7 系列,当前最新的版本是 5.7.15 GA 版本――这只有 600 M 多。

  最新的源代码放在GitHub 上,感兴趣的朋友可以去看看,其中有不少是中国人的贡献。

[1]: http://dev.mysql.com/doc/relnotes/mysql/8.0/en/

[2]: http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release-is-available/

[3]: http://dev.mysql.com/downloads/mysql/

MySQL中大对象的多版本并发控制详解

MySQL 8.0:InnoDB中大对象的MVCC

在本文中,我将解释MySQL InnoDB存储引擎中大对象(LOB)设计的多版本并发控制(MVCC) 。 MySQL 8.0有一个新功能,允许用户部分更新大型对象,包括JSON文档 。 使用此部分更新功能,当LOB部分更新时,MVCC对LOB的工作方式已发生变化。 对于正常更新(完整更新),MVCC将像以前的版本一样工作。 让我们看一下MVCC在不涉及部分更新时的工作原理,然后考虑对LOB进行部分更新的用例。

MVCC 常规更新

我使用术语常规更新来指代不是部分更新的更新。 我将通过一个例子解释MVCC如何用于常规更新大对象。 我将为此目的使用以下mtr(1)测试用例:

 create table t1 ( f1 int primary key , f2 longblob ) engine = innodb ; 
 insert into t1 values ( 1 , repeat ( 'a' , 65536 ) ) ; 
 
 start transaction ; 
 update t1 set f2 = repeat ( 'b' , 65536 ) where f1 = 1 ; 
 
 -- echo # Connection con1: 
 -- 对于使用MySQL客户端的用户,可能需要通过另开一个终端窗口建立新链接, 下同。
 connect ( con1 , localhost , root , , ) ; 
 -- echo # Must see the old value 'aaaaaaaaaa' 
 select f1 , right ( f2 , 10 ) from t1 order by f1 ; 
 
 -- echo # Connection default: 
 connection default ; 
 disconnect con1 ; 
 commit ; 
 
 drop table t1 ; 

为了理解下面的解释,仔细理解上述测试用例非常重要。

测试场景如下:

最初,表t1包含单个记录(R1)。
事务trx1将记录更新为新值。
当trx1仍处于活动状态时,另一个事务trx2正在读取记录。 它将读取旧值。

表t1仅包含一个记录(R1)。 但是trx1和trx2会看到两个不同的值。 该表实际上只包含最新值(trx1所见的值),而trx2看到的值或记录是从撤消日志记录中获得的。 让我们看下面的图片来更好地理解它。

初始状态:更新操作之前

下图显示了更新操作之前的情况。 撤消日志为空。 表的聚簇索引包含一行。 表中有一个LOB。 聚簇索引记录包含对LOB的引用。

最终状态:更新操作后

现在让我们看一下更新操作后的情况。

以下是一些重要的观察:

用户表空间中有两个LOB - 旧的LOB和新的LOB。 旧的LOB只能通过撤消日志访问。 聚集索引记录指向新LOB。
更新操作已创建包含更新向量的撤消日志记录。 此撤消日志记录指向旧LOB。
聚簇索引记录通过DB_ROLL_PTR系统列指向撤消日志记录。 此滚动指针指向撤消日志记录,该记录可用于构建聚簇索引记录的先前版本。
撤消记录不包含LOB本身。 而是它只包含对存储在用户表空间中的LOB的引用。
存储在撤消日志记录中的LOB引用与存储在聚簇索引记录中的LOB引用不同。

事务在连接1中采取的步骤如下:

事务查看R1并确定尚未提交修改聚簇索引记录的事务。 这意味着它无法读取该记录(因为默认隔离级别是REPEATABLE READ)。
它查看R1中的DB_ROLL_PTR并找到撤消日志记录。 使用撤消日志记录构建R1的先前版本。
它读取了这个构建的旧版R1。 请注意,此版本在聚簇索引记录中不可用。 但它使用撤消记录即时构建。
当R1指向新的LOB时,这个构造的旧版本的R1指向旧的LOB。 所以结果包含旧的LOB。

这是LOB的MVCC在不涉及部分更新时的工作方式。

MVCC部分更新

让我们看另一个例子,了解MVCC在部分更新的情况下是如何工作的。 我们需要另一个例子,因为目前仅通过函数json_set()和json_replace()支持JSON文档的部分更新。

 create table t2 ( f1 int primary key , j json ) engine = InnoDB ; 
 set @ elem_a = concat ( '"' , repeat ( 'a' , 200 ) , '"' ) ; 
 set @ elem_a_with_coma = concat ( @ elem_a , ',' ) ; 
 set @ json_doc = concat ( "[" , repeat ( @ elem_a_with_coma , 300 ) , @ elem_a , "]" ) ; 
 
 insert into t2 ( f1 , j ) values ( 1 , @ json_doc ) ; 
 
 start transaction ; 
 update t2 set j = json_set ( j , '$[200]' , repeat ( 'b' , 200 ) ) where f1 = 1 ; 
 
 -- echo # Connection con1: 
 connect ( con1 , localhost , root , , ) ; 
 -- echo # Must see the old value 'aaaaaaaaaa...' 
 select json_extract ( j , '$[200]' ) from t2 ; 
 
 -- echo # Connection default: 
 connection default ; 
 disconnect con1 ; 
 commit ; 

该场景与前面的示例相同。 只是longblob字段已更改为JSON文档。 加载的数据也略有不同,以符合JSON格式。

提示 :您可以在上述mtr测试用例(两者中)中添加语句set debug ='+ d,innodb_lob_print' ,以在服务器日志文件中打印LOB索引。 LOB索引将在插入后立即打印。 LOB索引将为您提供存储的LOB对象的结构。
在部分更新操作之前

完全或部分更新操作之前的初始条件是相同的,并且已经在上面给出。 但是在下图中,提供了一些附加信息。

让我们看看图中显示的其他信息:

存储在聚簇索引记录中的LOB引用现在包含LOB版本号v1。 在初始插入操作期间,将其设置为1,并在每次部分更新时递增。
每个LOB数据页面在LOB索引中都有一个条目。 每个条目都包含LOB版本信息。 每当修改一个LOB数据页时,它将被复制到具有新数据的新LOB数据页中,并且将创建具有递增的LOB版本号的新LOB索引条目。

附加信息是LOB版本号。 这在聚集索引记录中的LOB引用中以及LOB索引的每个条目中都可用。

部分更新操作后

下图说明了部分更新操作后的情况。

这里最重要的优化是用户表空间中仍然只有一个LOB。 仅更新需要修改的那些LOB数据页。 部分更新操作后的这个单个LOB包含旧版本和新版本的LOB。 图中LOB数据页面上的v1和v2标签说明了这一点。

另一个重要的观察是撤消日志和聚簇索引记录中的LOB引用指向同一个LOB。 但LOB引用包含不同的版本号。 撤消日志记录中的LOB引用包含v1(旧版本号),聚簇索引记录中的LOB引用包含新版本号v2。

LOB版本号的目的

如上所示,具有不同版本号的不同LOB引用指向相同的LOB。 单个LOB包含来自不同版本的部分。 LOB版本号用于获取各种LOB引用指向的正确版本。 在本节中,我们将了解如何完成此操作。

LOB索引包含组成LOB的LOB页面列表。 它包含LOB数据页的页码,每个LOB数据页包含的数据量以及版本号。 此列表的每个节点称为LOB索引条目。 每个LOB索引条目都包含旧版本的列表。 让我们看一个说明上述部分更新测试用例的结构的图。

最初,在完成部分更新之前,LOB索引总共包含4个条目。 四个条目的页码是5,6,7和8.没有LOB索引条目具有旧版本。 所有四个条目的版本号均为1。

部分更新完成后,我们注意到页码9已替换页码7,页码7现在被视为页码9的旧版本。页码9的版本号为2,并且页码7的版本号为1。

部分更新完成后,当通过版本号为1的LOB引用访问LOB时,将查看第5页的第一个索引条目。 它的版本号为1.如果索引条目中的版本号小于或等于 LOB引用中的版本号,则将读取该条目。 因此,将读取第5页。 然后将查看页码为6的索引条目。 它的版本号为1,因此将被读取。 然后将查看页码为9的索引条目。 它的版本号为2.但是lob引用的版本号为1.如果索引条目中的版本号大于LOB引用中的版本号,则不会读取该条目。 由于页码9的条目具有版本2,因此将查看其旧版本。 将检查页码为7的索引条目。 它的版本号为1,因此将被读取。 在此之后,将检查页码为8的索引条目。 它的版本号为1,因此也将被读取。 这是访问旧版LOB的方式。

部分更新完成后,当通过版本号为2的LOB引用访问LOB时,将查看第5页的第一个索引条目。 它的版本号为1.如果索引条目中的版本号小于或等于LOB引用中的版本号,则将读取该条目。 因此它将按顺序读取页码5,6,9,8。 由于版本号始终<= 2,因此无需使用旧版本访问页码7。

需要记住的一点是LOB在InnoDB中不是独立存在的。 它被视为聚簇索引记录的扩展。LOB对事务是否可见并不由LOB模块处理。 LOB模块只是处理聚簇索引记录。 如果事务访问LOB,则意味着它已经在聚簇索引记录中的DB_TRX_ID的帮助下确定它可以查看LOB(而不是LOB的特定版本)。 所以我们不担心LOB模块中的那个方面。 我们只专注于为给定的LOB版本号提供正确的内容。

结论

在本文中,我们了解了如何在InnoDB中为大对象完成MVCC。 当对LOB进行部分更新时,多个LOB引用可以指向同一个LOB。 但他们将拥有不同的版本号。 使用这些LOB版本号,可以访问正确的LOB内容。

希望您发现此信息有用。

谢谢你使用MySQL!

注释:

(1) Mtr即Mini-transaction的缩写,字面意思小事物,相对逻辑事物而言,我们把它称作物理事物。属于Innodb存储引擎的底层模块。主要用于锁和日志信息。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。