{"id":17401,"date":"2024-05-30T03:38:10","date_gmt":"2024-05-30T10:38:10","guid":{"rendered":"https:\/\/www.pingcap.com\/?post_type=article&#038;p=17401"},"modified":"2024-06-03T08:08:15","modified_gmt":"2024-06-03T15:08:15","slug":"revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/","title":{"rendered":"Revolutionizing MySQL Database Interactions with Text-to-SQL and LLMs"},"content":{"rendered":"<p>The integration of database technologies with advancements in artificial intelligence, particularly through Large Language Models (LLMs) such as OpenAI&#8217;s GPT-3, is a pivotal development in the realm of data management. Text-to-SQL(text2sql) capabilities enable users to convert natural language queries into precise SQL commands, democratizing data access and empowering users across varying levels of technical expertise.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Enhancing SQL Query Generation with LLMs<\/h2>\n\n\n\n<p>LLMs are at the forefront of this revolution, exhibiting an impressive ability to parse human language and generate corresponding SQL queries. This functionality is crucial for users without a background in SQL, allowing them to interact with databases more intuitively and efficiently.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Practical Examples and Table Definitions<\/h3>\n\n\n\n<p>Consider a scenario involving two essential MySQL database tables:<\/p>\n\n\n\n<p><strong>User Table<\/strong><\/p>\n\n\n\n<p>This table tracks users, with each record containing a unique identifier, the user&#8217;s name, and a reference to their associated school:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE User (\n    UserID INT PRIMARY KEY,\n    UserName VARCHAR(100),\n    SchoolID INT,\n    FOREIGN KEY (SchoolID) REFERENCES School(SchoolID)\n);<\/code><\/pre>\n\n\n\n<p><strong>School Table<\/strong><\/p>\n\n\n\n<p>This table details schools, including a unique identifier for each school, its name, and location:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE School (\n    SchoolID INT PRIMARY KEY,\n    SchoolName VARCHAR(255),\n    Location VARCHAR(255)\n);<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Query Examples Using Text-to-SQL<\/h3>\n\n\n\n<p>With the above table definitions in mind, LLMs can assist in generating SQL queries for commonly asked questions such as:<\/p>\n\n\n\n<p><strong>1.Total number of schools<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT COUNT(*) AS TotalSchools FROM School;<\/code><\/pre>\n\n\n\n<p><strong>2.Number of students per school<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT SchoolName, COUNT(*) AS StudentCount\nFROM User JOIN School ON User.SchoolID = School.SchoolID\nGROUP BY SchoolName;<\/code><\/pre>\n\n\n\n<p>These examples demonstrate the LLM\u2019s ability to derive structured queries from natural language inputs, simplifying complex data retrieval tasks.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Crafting Effective Prompts for LLMs<\/h3>\n\n\n\n<p>To maximize the effectiveness of LLMs in SQL generation, it is essential to construct detailed prompts that include clear questions and contextual information about the database schema. Here\u2019s a guideline on how to write these prompts:<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"1\">\n<li><strong>State the Question Clearly<\/strong>: Define precisely what information you need.<\/li>\n\n\n\n<li><strong>Describe the Schema<\/strong>: Include descriptions or SQL definitions of the tables involved.<\/li>\n\n\n\n<li><strong>Specify Any Conditions<\/strong>: Detail any specific conditions or filters for the query.<\/li>\n\n\n\n<li><strong>Request SQL Generation<\/strong>: Clearly indicate that you need an SQL query in response.<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">Example Prompt:<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>Given the following table definitions\n\n\u2014 'User' with fields 'UserID' (integer, primary key), 'UserName' (varchar, up to 100 characters), and 'SchoolID' (integer, foreign key linked to 'School');\n- 'School' with 'SchoolID' (integer, primary key), 'SchoolName' (varchar, up to 255 characters), and 'Location' (varchar, up to 255 characters)\n\ncould you generate an SQL query to answer question \"count the number of students at each school?\"<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>The capability of LLMs to transform natural language into executable SQL queries marks a significant advancement in the way we interact with databases. This innovation not only simplifies the querying process but also makes data more accessible to a broader audience, enhancing decision-making across various sectors. As we continue to explore the potential of these technologies, their integration promises to further streamline and enhance our data-driven world, ensuring that the complexities of database syntax are no longer a barrier to accessing valuable insights.<\/p>\n\n\n\n<p>If you want to try this simple demo, <a href=\"https:\/\/tidb.cloud\">TiDB Serverless<\/a> is a good playground for you to start, because it is a MySQL-compatible database and <a href=\"https:\/\/www.pingcap.com\/ko\/pricing\/\">FREE for 25GiB storage<\/a>.<\/p>\n\n\n\n<p>One more thing, TiDB Serverless has a Text-to-SQL built-in console. Read this <a href=\"https:\/\/docs.pingcap.com\/tidbcloud\/explore-data-with-chat2query\">doc<\/a> to learn more.<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong>Try Text-to-SQL with TiDB Serverless. <\/strong><\/mark><\/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:Start Instantly,position:article_bottom_cta\" rel=\"noopener\">Start Instantly<\/a><\/p>","protected":false},"excerpt":{"rendered":"<p>The integration of database technologies with advancements in artificial intelligence, particularly through Large Language Models (LLMs) such as OpenAI&#8217;s GPT-3, is a pivotal development in the realm of data management. Text-to-SQL(text2sql) capabilities enable users to convert natural language queries into precise SQL commands, democratizing data access and empowering users across varying levels of technical expertise. [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"template":"","class_list":["post-17401","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>Revolutionizing MySQL Interactions with Text-to-SQL and LLMs<\/title>\n<meta name=\"description\" content=\"Explore how to enhancing SQL query generation with LLMs, try TiDB Serverless with a Text-to-SQL built-in console.\" \/>\n<meta name=\"robots\" content=\"noindex, follow\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Revolutionizing MySQL Interactions with Text-to-SQL and LLMs\" \/>\n<meta property=\"og:description\" content=\"Explore how to enhancing SQL query generation with LLMs, try TiDB Serverless with a Text-to-SQL built-in console.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/\" \/>\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-03T15:08:15+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=\"3\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/\",\"url\":\"https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/\",\"name\":\"Revolutionizing MySQL Interactions with Text-to-SQL and LLMs\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"datePublished\":\"2024-05-30T10:38:10+00:00\",\"dateModified\":\"2024-06-03T15:08:15+00:00\",\"description\":\"Explore how to enhancing SQL query generation with LLMs, try TiDB Serverless with a Text-to-SQL built-in console.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/#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\":\"Revolutionizing MySQL Database Interactions with Text-to-SQL and LLMs\"}]},{\"@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":"Revolutionizing MySQL Interactions with Text-to-SQL and LLMs","description":"Explore how to enhancing SQL query generation with LLMs, try TiDB Serverless with a Text-to-SQL built-in console.","robots":{"index":"noindex","follow":"follow"},"og_locale":"ko_KR","og_type":"article","og_title":"Revolutionizing MySQL Interactions with Text-to-SQL and LLMs","og_description":"Explore how to enhancing SQL query generation with LLMs, try TiDB Serverless with a Text-to-SQL built-in console.","og_url":"https:\/\/www.pingcap.com\/ko\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_modified_time":"2024-06-03T15:08:15+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":"3\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/","url":"https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/","name":"Revolutionizing MySQL Interactions with Text-to-SQL and LLMs","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"datePublished":"2024-05-30T10:38:10+00:00","dateModified":"2024-06-03T15:08:15+00:00","description":"Explore how to enhancing SQL query generation with LLMs, try TiDB Serverless with a Text-to-SQL built-in console.","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/article\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/#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":"Revolutionizing MySQL Database Interactions with Text-to-SQL and LLMs"}]},{"@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\/revolutionizing-mysql-database-interactions-with-text-to-sql-and-llms\/\">            <h3>Revolutionizing MySQL Database Interactions with Text-to-SQL and LLMs<\/h3>            <p>The integration of database technologies with advancements in artificial intelligence, particularly through Large Language Models (LLMs) such as OpenAI&#8217;s GPT-3, is a pivotal development in the realm of data management. Text-to-SQL(text2sql) capabilities enable users to convert natural language queries into precise SQL commands, democratizing data access and empowering users across varying levels of technical expertise. [&hellip;]<\/p>        <\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article\/17401","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=17401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}