{"id":17531,"date":"2024-06-06T09:17:24","date_gmt":"2024-06-06T16:17:24","guid":{"rendered":"https:\/\/www.pingcap.com\/?post_type=article&#038;p=17531"},"modified":"2024-06-06T09:18:58","modified_gmt":"2024-06-06T16:18:58","slug":"what-is-mysql-view","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/what-is-mysql-view\/","title":{"rendered":"What is MySQL View?"},"content":{"rendered":"<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Introduction_to_MySQL_Views\"><\/span>Introduction to MySQL Views<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>A MySQL view is a virtual table created by a query that combines the results of a SELECT statement. It provides a way to simplify complex queries by abstracting underlying database schema details. Unlike temporary tables, views do not store data; they always reflect the latest data from the base tables whenever queried. This makes views an essential tool for various database operations.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">How is a Temporary Table Different from MySQL View?<\/h3>\n\n\n\n<p>Temporary tables and views both serve to simplify SQL operations, but they operate quite differently. A temporary table is a real table that temporarily stores data for the duration of a session or transaction. Once the operation or session is complete, the data in the temporary table is lost. In contrast, a <em>MySQL<\/em><em> view<\/em> is a predefined SELECT query that operates on existing data without duplicating it. This means that views can always show the most recent data available in the database, while temporary tables cannot.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Advantages of MySQL Views<\/h3>\n\n\n\n<p>Views offer several benefits in managing databases effectively and securely.<\/p>\n\n\n\n<p><strong>1.Simplify Complex Queries<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"1\"><\/ol>\n\n\n\n<p>Using views can significantly simplify complex queries. By abstracting complex SQL logic into a view, queries become more readable and maintainable. For example, a query involving multiple joins and aggregations can be encapsulated into a single view, making it easier to work with.<\/p>\n\n\n\n<p><strong>2.Make the Business Logic Consistent<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\"><\/ol>\n\n\n\n<p>Views help maintain consistent business logic across the database. Since views represent a single source of truth for complex logic and calculations, any change in the logic is reflected everywhere the view is used. This ensures consistent results across different queries and applications.<\/p>\n\n\n\n<p><strong>3.Add Extra Security Layers<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\"><\/ol>\n\n\n\n<p>Views can add an extra layer of security by exposing only the necessary data to the user. Sensitive data can be filtered out at the view level, ensuring that only authorized personnel have access to it. This is particularly useful in multi-tenant applications where data isolation is critical.<\/p>\n\n\n\n<p><strong>4.Enable Backward Compatibility<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\"><\/ol>\n\n\n\n<p>In cases where the database schema changes, views can act as an interface layer to ensure backward compatibility. Applications can continue using the old schema through views, even if the underlying table structure has changed. This minimizes the impact on existing applications during database migrations.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Managing_Views_in_MySQL\"><\/span>Managing Views in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Create View<\/h3>\n\n\n\n<p>Creating a view in MySQL is straightforward. The following example demonstrates how to create a view named <code>active_users<\/code>, which lists users who have logged in within the last 30 days.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE VIEW active_users AS\nSELECT id, username, last_login\nFROM users\nWHERE last_login &gt;= DATE_SUB(CURDATE(), INTERVAL 30 DAY);<\/code><\/pre>\n\n\n\n<p>Creating a view that encapsulates data from multiple tables can be highly beneficial for complex reporting and analytical tasks. By using a JOIN clause within the view definition, you can combine columns from different tables, presenting an integrated and comprehensive dataset.<\/p>\n\n\n\n<p>Below is an example demonstrating how to create a view named <code>`user_order_summary`<\/code>, which provides an overview of user information along with their recent order details.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE VIEW user_order_summary AS\nSELECT \n    users.id, \n    users.username, \n    orders.order_id, \n    orders.order_date, \n    orders.total_amount\nFROM \n    users\nJOIN \n    orders ON users.id = orders.user_id\nWHERE \n    orders.order_date &gt;= DATE_SUB(CURDATE(), INTERVAL 30 DAY);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Query View<\/h3>\n\n\n\n<p>Querying a view is as simple as querying a regular table. For example, to retrieve data from the <code>active_users<\/code> view:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM active_users;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Update View<\/h3>\n\n\n\n<p>Views cannot be directly updated in MySQL, but you can modify the underlying data through the view if the view meets certain conditions, such as being updatable. Alternatively, you can recreate the view to update its definition:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE OR REPLACE VIEW active_users AS\nSELECT id, username, last_login\nFROM users\nWHERE last_login &gt;= DATE_SUB(CURDATE(), INTERVAL 60 DAY);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Drop View<\/h3>\n\n\n\n<p>Dropping a view permanently removes it from the database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP VIEW IF EXISTS active_users;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Limitations_and_Considerations_when_Working_with_Views\"><\/span>Limitations and Considerations when Working with Views<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p><strong>Performance Implications<\/strong><\/p>\n\n\n\n<p>While views make queries simpler, they can sometimes lead to performance issues if not used prudently. Since views are executed each time they are queried, complex views can result in heavy processing loads. It is advisable to test performance implications, especially in resource-intensive environments.<\/p>\n\n\n\n<p><strong>View Dependencies<\/strong><\/p>\n\n\n\n<p>Managing dependencies is crucial when working with views. Since views depend on underlying tables, altering or dropping those tables can lead to broken views. MySQL does not automatically update the view definitions when base tables are altered, which may require additional maintenance.<\/p>\n\n\n\n<p><strong>Security Considerations<\/strong><\/p>\n\n\n\n<p>Although views provide an additional layer of security by obfuscating underlying table structures, they are not a substitute for proper user permissions and access controls. Ensure that views are used as a complementary measure to your existing security policies.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Summary\"><\/span>Summary<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>MySQL views offer a powerful way to abstract and simplify complex SQL queries, maintaining business logic consistency, adding an extra security layer, and ensuring backward compatibility. While they come with performance and dependency considerations, their benefits often outweigh these drawbacks. For robust and efficient database management, especially for complex queries, incorporating views can be a valuable strategy.<\/p>\n\n\n\n<p><a href=\"\/ko\/tidb\/\">\ud2f0DB<\/a>, a MySQL-compatible distributed SQL database, also supports views, making it an excellent choice for enterprises looking to leverage the power of MySQL views in scalable, distributed environments. <a href=\"https:\/\/tidb.cloud\/\">TiDB Serverless<\/a> is a fully managed database service with up to 25GiB Free Storage. It can effortlessly scale from zero to unlimited transactions. <\/p>\n\n\n\n<p><a href=\"https:\/\/tidbcloud.com\/free-trial\/\" class=\"button\" target=\"_blank\" data-gtag=\"event:go_to_cloud_signup,product_type:serverless,button_name:Try TiDB Serverless,position:article_middle_cta\" rel=\"noopener\">Try TiDB Serverless<\/a><\/p>","protected":false},"excerpt":{"rendered":"<p>Introduction to MySQL Views A MySQL view is a virtual table created by a query that combines the results of a SELECT statement. It provides a way to simplify complex queries by abstracting underlying database schema details. Unlike temporary tables, views do not store data; they always reflect the latest data from the base tables [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"template":"","class_list":["post-17531","article","type-article","status-publish","hentry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>What is MySQL View? | TiDB<\/title>\n<meta name=\"description\" content=\"A MySQL view is a virtual table created by a query that combines the results of a SELECT statement. Learn how to manage views in MySQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pingcap.com\/ko\/article\/what-is-mysql-view\/\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What is MySQL View? | TiDB\" \/>\n<meta property=\"og:description\" content=\"A MySQL view is a virtual table created by a query that combines the results of a SELECT statement. Learn how to manage views in MySQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/article\/what-is-mysql-view\/\" \/>\n<meta property=\"og:site_name\" content=\"TiDB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/pingcap2015\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-06T16:18:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1440\" \/>\n\t<meta property=\"og:image:height\" content=\"714\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/\",\"url\":\"https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/\",\"name\":\"What is MySQL View? | TiDB\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"datePublished\":\"2024-06-06T16:17:24+00:00\",\"dateModified\":\"2024-06-06T16:18:58+00:00\",\"description\":\"A MySQL view is a virtual table created by a query that combines the results of a SELECT statement. Learn how to manage views in MySQL.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pingcap.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Articles\",\"item\":\"https:\/\/www.pingcap.com\/article\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"What is MySQL View?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pingcap.com\/#website\",\"url\":\"https:\/\/www.pingcap.com\/\",\"name\":\"TiDB\",\"description\":\"TiDB | SQL at Scale\",\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pingcap.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pingcap.com\/#organization\",\"name\":\"PingCAP\",\"url\":\"https:\/\/www.pingcap.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"width\":811,\"height\":232,\"caption\":\"PingCAP\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/facebook.com\/pingcap2015\",\"https:\/\/x.com\/PingCAP\",\"https:\/\/linkedin.com\/company\/pingcap\",\"https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"What is MySQL View? | TiDB","description":"A MySQL view is a virtual table created by a query that combines the results of a SELECT statement. Learn how to manage views in MySQL.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pingcap.com\/ko\/article\/what-is-mysql-view\/","og_locale":"ko_KR","og_type":"article","og_title":"What is MySQL View? | TiDB","og_description":"A MySQL view is a virtual table created by a query that combines the results of a SELECT statement. Learn how to manage views in MySQL.","og_url":"https:\/\/www.pingcap.com\/ko\/article\/what-is-mysql-view\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_modified_time":"2024-06-06T16:18:58+00:00","og_image":[{"width":1440,"height":714,"url":"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_site":"@PingCAP","twitter_misc":{"Est. reading time":"4\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/","url":"https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/","name":"What is MySQL View? | TiDB","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"datePublished":"2024-06-06T16:17:24+00:00","dateModified":"2024-06-06T16:18:58+00:00","description":"A MySQL view is a virtual table created by a query that combines the results of a SELECT statement. Learn how to manage views in MySQL.","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/article\/what-is-mysql-view\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pingcap.com\/"},{"@type":"ListItem","position":2,"name":"Articles","item":"https:\/\/www.pingcap.com\/article\/"},{"@type":"ListItem","position":3,"name":"What is MySQL View?"}]},{"@type":"WebSite","@id":"https:\/\/www.pingcap.com\/#website","url":"https:\/\/www.pingcap.com\/","name":"\ud2f0DB","description":"TiDB | SQL at Scale","publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pingcap.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ko-KR"},{"@type":"Organization","@id":"https:\/\/www.pingcap.com\/#organization","name":"PingCAP","url":"https:\/\/www.pingcap.com\/","logo":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/","url":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","width":811,"height":232,"caption":"PingCAP"},"image":{"@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/pingcap2015","https:\/\/x.com\/PingCAP","https:\/\/linkedin.com\/company\/pingcap","https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA"]}]}},"card_markup":"        <a class=\"card-article\" href=\"https:\/\/www.pingcap.com\/ko\/article\/what-is-mysql-view\/\">            <h3>What is MySQL View?<\/h3>            <p>Introduction to MySQL Views A MySQL view is a virtual table created by a query that combines the results of a SELECT statement. It provides a way to simplify complex queries by abstracting underlying database schema details. Unlike temporary tables, views do not store data; they always reflect the latest data from the base tables [&hellip;]<\/p>        <\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article\/17531","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article"}],"about":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/types\/article"}],"author":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/users\/8"}],"wp:attachment":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media?parent=17531"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}