{"id":6949,"date":"2017-09-06T11:26:37","date_gmt":"2017-09-06T03:26:37","guid":{"rendered":"http:\/\/rmohan.com\/?p=6949"},"modified":"2017-09-06T11:26:37","modified_gmt":"2017-09-06T03:26:37","slug":"mysql-5-7-vs-mysql-8-0-whats-new-in-mysql-8-0","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=6949","title":{"rendered":"MySQL 5.7 vs MySQL 8.0 \u2013 What\u2019s new in MySQL 8.0?"},"content":{"rendered":"<p>MySQL is arguably the most popular\u00a0and common\u00a0RDBMS. The last major\u00a0release was MySQL 5.7 back in 2013.<\/p>\n<p>As of today, several development milestones were declared for MySQL v8.0, but there is no\u00a0official release available yet.<\/p>\n<div class=\"in-content-promo\">Tip: Do not click here if all your SQL queries are fast as lightning!<\/div>\n<p>Even though there is no official software release to download and use yet, the list of features for MySQL 8.0 was officially announced, which makes it a great point in time to talk about it and share these great enhancements with you.<\/p>\n<p>So without further ado, let\u2019s dive into listing the new comers to MySQL 8.0:<\/p>\n<ul>\n<li><strong>Roles<\/strong>\u00a0\u2013\u00a0Very exciting feature that allows to create roles in MySQL server and assign specific privileges to them. These roles can be assigned to users. So from now you don\u2019t have to remember which permissions a programmer from team X needs, and should a QA\u00a0from team Y needs privilege Z. Also, it\u2019s very easy to set up:\n<ul>\n<li>Creating a new role:\n<div>\n<div id=\"highlighter_861211\" class=\"syntaxhighlighter nogutter  sql\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"sql keyword\">CREATE<\/code> <code class=\"sql plain\">ROLE <\/code><code class=\"sql string\">'app_developer'<\/code><code class=\"sql plain\">, <\/code><code class=\"sql string\">'app_read'<\/code><code class=\"sql plain\">, <\/code><code class=\"sql string\">'app_write'<\/code><code class=\"sql plain\">;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/li>\n<li>Assigning privileges to roles:\n<div>\n<div id=\"highlighter_241134\" class=\"syntaxhighlighter nogutter  sql\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"sql keyword\">GRANT<\/code> <code class=\"sql keyword\">SELECT<\/code> <code class=\"sql keyword\">ON<\/code> <code class=\"sql plain\">app_db.* <\/code><code class=\"sql keyword\">TO<\/code> <code class=\"sql string\">'app_read'<\/code><code class=\"sql plain\">;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/li>\n<li>Assigning the role to a user:\n<div>\n<div id=\"highlighter_21323\" class=\"syntaxhighlighter nogutter  sql\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"sql keyword\">GRANT<\/code> <code class=\"sql string\">'app_read'<\/code> <code class=\"sql keyword\">TO<\/code> <code class=\"sql string\">'read_user1'<\/code><code class=\"sql plain\">@<\/code><code class=\"sql string\">'localhost'<\/code><code class=\"sql plain\">, <\/code><code class=\"sql string\">'read_user2'<\/code><code class=\"sql plain\">@<\/code><code class=\"sql string\">'localhost'<\/code><code class=\"sql plain\">;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/li>\n<\/ul>\n<\/li>\n<li><strong>Invisible index<\/strong>\u00a0\u2013 ever wanted to just hide an index you currently don\u2019t need, without actually dropping it? Now you can. If you\u2019re not sure if you need an index, you can mark it as invisible and MySQL optimizer won\u2019t use it. After monitoring for your server and queries performance, you can decide to re-activate it in case you believe it will result in\u00a0a performance boost.<\/li>\n<li><strong>Configuration Persistence\u00a0\u2013\u00a0<\/strong>Changing configuration during MySQL runtime is commonly done using\u00a0<em>SET GLOBAL<\/em>. This disadvantage in this technique is that the changes will not survive a server restart. here comes\u00a0<em>SET PERSIST<\/em>\u00a0to the rescue, which does exactly that, apply configuration changes which survive a MySQL server restart.<\/li>\n<li><strong>Default character set and collation \u2013\u00a0<\/strong>Starting MySQL 8.0, the default character set will be\u00a0<em>utf8mb4<\/em>\u00a0and the collation will be\u00a0<em>utf8mb4_800_ci_ai<\/em>. These are great news and one step ahead towards standardized multilingual support in data driven applications.<\/li>\n<li><strong>UUID Enhancements<\/strong>\u00a0\u2013 UUIDs are usually used to generate unique ids in tables. Starting this new version, MySQL can hold these values in a VARBINARY(16) column instead of CHAR(36).\u00a0The impact of this change is better storage usage and performance improvement. Also, three new functions were introduced to handle these UUID values:\u00a0BIN_TO_UUID(), UUID_TO_BIN(), IS_UUID().<\/li>\n<li><strong>Cost Model Improvements<\/strong>\u00a0\u2013 for the first time, MySQL\u2019s cost model will look into the memory and check if the relevant data for the query already resides in memory. As a result of this change, different query plans can be chosen. this will happen automatically without need for extra configuration.<\/li>\n<li><strong>Descending Indexes \u2013\u00a0<\/strong>MySQL now allows to create descending indexes and scan them in a reverse order, without performance penalty. This was possible in the past, but you would have to take the performance hit on that.<\/li>\n<li><strong>Common Table Expressions \u2013\u00a0<\/strong>CTE is a new feature (which is already available in other databases) that will simplify the way you write complex queries. To put it in simple words, using this feature (the WITH select)\u00a0will automatically create a temporary table behind the scenes, which you can use in the same query and refer to it. This temporary table \/ view can only be used in that single query. This feature introduces\u00a0both readability and performance improvements.<\/li>\n<\/ul>\n<p>I\u2019m very excited and look forward to have see this software version released. Many great enhancements are introduced by the MySQL team in v8.0. Keep tuned for more information soon.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL is arguably the most popular and common RDBMS. The last major release was MySQL 5.7 back in 2013.<\/p>\n<p>As of today, several development milestones were declared for MySQL v8.0, but there is no official release available yet.<\/p>\n<p> Tip: Do not click here if all your SQL queries are fast as lightning! <\/p>\n<p>Even though there [&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6949"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6949"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6949\/revisions"}],"predecessor-version":[{"id":6950,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6949\/revisions\/6950"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6949"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6949"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6949"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}