{"id":6012,"date":"2018-11-28T09:12:28","date_gmt":"2018-11-28T09:12:28","guid":{"rendered":"https:\/\/dbtut.com\/?p=6012"},"modified":"2018-12-05T19:27:37","modified_gmt":"2018-12-05T19:27:37","slug":"pay-attention-when-using-mysql-user-defined-variables","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/","title":{"rendered":"Pay attention when using MySQL User-Defined Variables"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>Recently, i used a\u00a0user-defined\u00a0variable to store some values, and it showed an unexpected\u00a0result. I updated rows using &#8216;where a in (@var)&#8217;, but this query just updated the first value of &#8216;@var&#8217;, instead updated all matched values. This article introduces how to store multiple values in a user-defined variables correctly, when using such variables, make sure the number of\u00a0Rows matched, Changed,\u00a0affected\u00a0is right.<\/p>\n<p><strong>MySQL Version: 8.0.12<\/strong><\/p>\n<h1>Problem Statement<\/h1>\n<pre class=\"lang:default decode:true\">CREATE TABLE `t2` (\r\n\r\n\u00a0 `a` int(11) NOT NULL AUTO_INCREMENT,\r\n\r\n\u00a0 `b` int(11) DEFAULT NULL,\r\n\r\n\u00a0 `c` int(11) DEFAULT NULL,\r\n\r\n\u00a0 PRIMARY KEY (`a`),\r\n\r\n\u00a0 UNIQUE KEY `uniq_b` (`b`)\r\n\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8\r\n\r\n\r\nmysql&gt; select * from t2 where b&gt;100;\r\n\r\n+------+------+------+\r\n\r\n| a\u00a0 \u00a0 | b\u00a0 \u00a0 | c\u00a0 \u00a0 |\r\n\r\n+------+------+------+\r\n\r\n|\u00a0 123 |\u00a0 234 |\u00a0 345 |\r\n\r\n| 1234 | 2345 | 3456 |\r\n\r\n+------+------+------+<\/pre>\n<p>&nbsp;<\/p>\n<p>First step, I store the result values in a variable:<\/p>\n<pre class=\"lang:default decode:true\">set @tmpvar=(select<strong> group_concat(a)<\/strong> from t2 where b&gt;100);\r\n\r\nmysql&gt; select @tmpvar;\r\n\r\n+----------+\r\n\r\n| @tmpvar\u00a0 |\r\n\r\n+----------+\r\n\r\n| 123,1234 |\r\n\r\n+----------+<\/pre>\n<p>&nbsp;<\/p>\n<p>Secondly, update the table rows:<\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; update t2 set c=300 where a in (@tmpvar);\r\n\r\nQuery OK,\u00a0<strong>1 row affected<\/strong>\u00a0(0.14 sec)\r\n\r\n<strong>Rows matched: 1\u00a0 Changed: 1<\/strong>\u00a0 Warnings: 0\r\n\r\n\r\nmysql&gt; select * from t2 where b&gt;100;\r\n\r\n+------+------+------+\r\n\r\n| a\u00a0 \u00a0 | b\u00a0 \u00a0 | c\u00a0 \u00a0 |\r\n\r\n+------+------+------+\r\n\r\n|\u00a0 123 |\u00a0 234 |\u00a0 300 |\r\n\r\n| 1234 | 2345 | 3456 |\r\n\r\n+------+------+------+\r\n\r\n2 rows in set (0.00 sec)<\/pre>\n<p>In this step, after the update query is finished, the result shows, &#8220;<strong>1 row affected<\/strong>&#8220;, but there are 2 members in &#8216;@tmpvar&#8217;. Then i search the\u00a0User-Defined Variables part of MySQL Server\u00a0Reference Manual(<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/user-variables.html\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/user-variables.html<\/a>).<\/p>\n<p><b>&#8220;If the value of a user variable is selected in a result set, it is returned to the client as a string.&#8221;<\/b><\/p>\n<p>So when i store two values(123,1234) in &#8216;@tmpvar&#8217;, &#8216;@tmpvar&#8217; returns me a string &#8216;123,1234&#8217; actually, not &#8216;123&#8217; and &#8216;1234&#8217;, and the update result is correct in this case. Also we can have a test.<\/p>\n<p>&nbsp;<\/p>\n<h1>Troubleshooting<\/h1>\n<pre class=\"lang:default decode:true\">mysql&gt; set @tmpvar=(select group_concat(a) from t2 where b&gt;100);\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n\r\nmysql&gt; select @tmpvar;\r\n\r\n+----------+\r\n\r\n| @tmpvar\u00a0 |\r\n\r\n+----------+\r\n\r\n| 123,1234 |\r\n\r\n+----------+\r\n\r\n1 row in set (0.00 sec)\r\n\r\n\r\nmysql&gt; update t2 set c=100 where a in (@tmpvar);\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n<strong>Rows matched: 1\u00a0<\/strong>\u00a0Changed: 0\u00a0 Warnings: 0\r\n\r\n\r\nmysql&gt; update t2 set c=100 where a in (<strong>cast('123,1234' as char)<\/strong>);\r\n\r\nQuery OK, 0 rows affected (0.01 sec)\r\n\r\n<strong>Rows matched: 1\u00a0<\/strong>\u00a0Changed: 0\u00a0 Warnings: 0<\/pre>\n<p>I convert\u00a0the <strong>&#8216;123,1234&#8217; <\/strong>as a character string, then we get &#8220;<strong>Rows matched: 1<\/strong>&#8221; for these two update queries. So this is not a bug of MySQL, it&#8217;s just when using a user-defined variable to store multiple values, it is returned to the client as a string.<\/p>\n<p>&nbsp;<\/p>\n<h1>How to solve?<\/h1>\n<p>Then how can we use the\u00a0user-defined\u00a0variable to store multiple values and return expected result? I found two ways.<\/p>\n<h2>1. Splice\u00a0a\u00a0complete statement<\/h2>\n<pre class=\"lang:default decode:true\">mysql&gt; set @tmpvar=(select group_concat(a) from t2 where b&gt;100);\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n\r\nmysql&gt; select @tmpvar;\r\n\r\n+----------+\r\n\r\n| @tmpvar\u00a0 |\r\n\r\n+----------+\r\n\r\n| 123,1234 |\r\n\r\n+----------+\r\n\r\n1 row in set (0.00 sec)\r\n\r\n\r\nmysql&gt; set @sql=(<strong>concat(\"update t2 set c=555 where a in ( \",@tmpvar, \" )\" )<\/strong> );\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n\r\nmysql&gt; PREPARE stmt FROM @sql;\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Query OK, 0 rows affected (0.00 sec)\r\n\r\nStatement prepared\r\n\r\n\r\nmysql&gt; execute stmt;\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Query OK,\u00a02 rows affected\u00a0(0.07 sec)\r\n\r\n<strong>Rows matched: 2\u00a0 Changed: 2\u00a0<\/strong> Warnings: 0\r\n\r\n\r\nmysql&gt; select * from t2 where b&gt;100;\r\n\r\n+------+------+------+\r\n\r\n| a\u00a0 \u00a0 | b\u00a0 \u00a0 | c\u00a0 \u00a0 |\r\n\r\n+------+------+------+\r\n\r\n|\u00a0 123 |\u00a0 234 |\u00a0 555 |\r\n\r\n| 1234 | 2345 |\u00a0 555 |\r\n\r\n+------+------+------+\r\n\r\n2 rows in set (0.00 sec)<\/pre>\n<p>In this way, i\u00a0splice the &#8216;@tmpvar&#8217; and other part of queries into a\u00a0complete statement named\u00a0&#8216;@sql&#8217;, and then execute this statement.<\/p>\n<p>&nbsp;<\/p>\n<h2>2. Use the\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/string-functions.html#function_find-in-set\" target=\"_blank\" rel=\"noopener\">FIND_IN_SET<\/a>\u00a0function<\/h2>\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/string-functions.html#function_find-in-set\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/string-functions.html#function_find-in-set<\/a><\/p>\n<pre class=\"lang:default decode:true\">mysql&gt; set @tmpvar=(select group_concat(a) from t2 where b&gt;100);\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\n\r\nmysql&gt; select @tmpvar;\r\n\r\n+----------+\r\n\r\n| @tmpvar\u00a0 |\r\n\r\n+----------+\r\n\r\n| 123,1234 |\r\n\r\n+----------+\r\n\r\n1 row in set (0.01 sec)\r\n\r\n\r\nmysql&gt; update t2 set c=100 where FIND_IN_SET(a,@tmpvar);\r\n\r\nQuery OK,\u00a0<strong>2 rows affected<\/strong>\u00a0(0.15 sec)\r\n\r\n<strong>Rows matched: 2\u00a0 Changed: 2\u00a0<\/strong> Warnings: 0\r\n\r\n\r\nmysql&gt; select * from t2 where b&gt;100;\r\n\r\n+------+------+------+\r\n\r\n| a\u00a0 \u00a0 | b\u00a0 \u00a0 | c\u00a0 \u00a0 |\r\n\r\n+------+------+------+\r\n\r\n|\u00a0 123 |\u00a0 234 |\u00a0 100 |\r\n\r\n| 1234 | 2345 |\u00a0 100 |\r\n\r\n+------+------+------+\r\n\r\n2 rows in set (0.00 sec)<\/pre>\n<p>The second way is easier, just replacing the &#8216;where &#8230; in &#8216; with the\u00a0FIND_IN_SET function. And\u00a0FIND_IN_SET will treat &#8216;@tmpvar&#8217; as a list, then search for column &#8220;a&#8221; within the list.<\/p>\n\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_6012\" class=\"pvc_stats all  \" data-element-id=\"6012\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Recently, i used a\u00a0user-defined\u00a0variable to store some values, and it showed an unexpected\u00a0result. I updated rows using &#8216;where a in (@var)&#8217;, but this query just updated the first value of &#8216;@var&#8217;, instead updated all matched values. This article introduces how to store multiple values in a user-defined variables correctly, when using such variables, make &hellip;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_6012\" class=\"pvc_stats all  \" data-element-id=\"6012\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":50,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[7],"tags":[1301,1560],"class_list":["post-6012","post","type-post","status-publish","format-standard","","category-mysql","tag-mysql","tag-user-defined-variable"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":132,"today_views":0},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Pay attention when using MySQL User-Defined Variables - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How to store multiple values in a user-defined variables correctly, when using such variables, make sure the number of\u00a0Rows matched, Changed,\u00a0affected\u00a0is right.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Pay attention when using MySQL User-Defined Variables - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How to store multiple values in a user-defined variables correctly, when using such variables, make sure the number of\u00a0Rows matched, Changed,\u00a0affected\u00a0is right.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-11-28T09:12:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-12-05T19:27:37+00:00\" \/>\n<meta name=\"author\" content=\"Shuo Wang\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Shuo Wang\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/\"},\"author\":{\"name\":\"Shuo Wang\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/83d7028b846c78bfd03244ec6cd04289\"},\"headline\":\"Pay attention when using MySQL User-Defined Variables\",\"datePublished\":\"2018-11-28T09:12:28+00:00\",\"dateModified\":\"2018-12-05T19:27:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/\"},\"wordCount\":360,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"keywords\":[\"MySQL\",\"user-defined variable\"],\"articleSection\":[\"MySQL-MariaDB\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/\",\"name\":\"Pay attention when using MySQL User-Defined Variables - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-11-28T09:12:28+00:00\",\"dateModified\":\"2018-12-05T19:27:37+00:00\",\"description\":\"How to store multiple values in a user-defined variables correctly, when using such variables, make sure the number of\u00a0Rows matched, Changed,\u00a0affected\u00a0is right.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Pay attention when using MySQL User-Defined Variables\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/83d7028b846c78bfd03244ec6cd04289\",\"name\":\"Shuo Wang\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/da30aa3d0157ae5f1f853927f56b868cfb1d801888eb954768c471e5d41cdd87?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/da30aa3d0157ae5f1f853927f56b868cfb1d801888eb954768c471e5d41cdd87?s=96&d=mm&r=g\",\"caption\":\"Shuo Wang\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/shuowang\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Pay attention when using MySQL User-Defined Variables - Database Tutorials","description":"How to store multiple values in a user-defined variables correctly, when using such variables, make sure the number of\u00a0Rows matched, Changed,\u00a0affected\u00a0is right.","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:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/","og_locale":"en_US","og_type":"article","og_title":"Pay attention when using MySQL User-Defined Variables - Database Tutorials","og_description":"How to store multiple values in a user-defined variables correctly, when using such variables, make sure the number of\u00a0Rows matched, Changed,\u00a0affected\u00a0is right.","og_url":"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/","og_site_name":"Database Tutorials","article_published_time":"2018-11-28T09:12:28+00:00","article_modified_time":"2018-12-05T19:27:37+00:00","author":"Shuo Wang","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Shuo Wang","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/"},"author":{"name":"Shuo Wang","@id":"https:\/\/dbtut.com\/#\/schema\/person\/83d7028b846c78bfd03244ec6cd04289"},"headline":"Pay attention when using MySQL User-Defined Variables","datePublished":"2018-11-28T09:12:28+00:00","dateModified":"2018-12-05T19:27:37+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/"},"wordCount":360,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"keywords":["MySQL","user-defined variable"],"articleSection":["MySQL-MariaDB"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/","url":"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/","name":"Pay attention when using MySQL User-Defined Variables - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-11-28T09:12:28+00:00","dateModified":"2018-12-05T19:27:37+00:00","description":"How to store multiple values in a user-defined variables correctly, when using such variables, make sure the number of\u00a0Rows matched, Changed,\u00a0affected\u00a0is right.","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/11\/28\/pay-attention-when-using-mysql-user-defined-variables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Pay attention when using MySQL User-Defined Variables"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/83d7028b846c78bfd03244ec6cd04289","name":"Shuo Wang","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/da30aa3d0157ae5f1f853927f56b868cfb1d801888eb954768c471e5d41cdd87?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/da30aa3d0157ae5f1f853927f56b868cfb1d801888eb954768c471e5d41cdd87?s=96&d=mm&r=g","caption":"Shuo Wang"},"url":"https:\/\/dbtut.com\/index.php\/author\/shuowang\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6012","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/50"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=6012"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6012\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=6012"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=6012"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=6012"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}