{"id":61809,"date":"2021-10-07T08:58:15","date_gmt":"2021-10-07T13:58:15","guid":{"rendered":"https:\/\/blog.cpanel.com\/?p=61809"},"modified":"2021-10-07T08:58:15","modified_gmt":"2021-10-07T13:58:15","slug":"varchar-vs-text-for-mysql-databases","status":"publish","type":"post","link":"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/","title":{"rendered":"VARCHAR vs. TEXT for MySQL Databases"},"content":{"rendered":"\n<p>When you\u2019re building a database for a web application, one of the most impactful decisions is the data type you choose for text data fields. MySQL provides multiple string data types, each with unique characteristics and trade-offs. The data type you choose affects how much data you can store, where it\u2019s stored, the functionality available to you, and the performance of everyday database operations.<\/p>\n\n\n\n<p>In this article, we\u2019ll look at one frequently asked question concerning MySQL string data types: should you use a VARCHAR column or a TEXT column to store textual website data?<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>MySQL VARCHAR vs. TEXT: The Short Answer<\/strong><\/h2>\n\n\n\n<p>If you\u2019re looking for a TL;DR, it\u2019s this: use VARCHAR if your data is of variable length and you know it fits into VARCHAR\u2019s 65,535 character limit. In most circumstances, VARCHAR provides better performance, it\u2019s more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>MySQL VARCHAR vs. TEXT: The Longer Answer<\/strong><\/h2>\n\n\n\n<p>To explain why VARCHAR is the right default choice, we\u2019ll need to look at MySQL\u2019s string data formats. For our purposes, these fall into two main groups, CHAR and VARCHAR types and the TEXT types, which are based on MySQL\u2019s binary object BLOB types. There are other string types\u2014SET and ENUM\u2014but they\u2019re not relevant to this article.<\/p>\n\n\n\n<p>We\u2019ll be talking about characters and the amount of space they consume. However, we\u2019re not going to discuss the precise definition of a character or the effect of using different character sets. Take a look at the MySQL documentation to learn more about <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/storage-requirements.html\" target=\"_blank\" rel=\"noreferrer noopener\">character lengths and multibyte character sets<\/a>.<\/p>\n\n\n\n<p>First, CHAR and VARCHAR:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>CHAR is a fixed-length column with a maximum length of 255 characters. You declare the size when you create the table. If you store fewer characters than the fixed length, MySQL pads the remainder with spaces. Therefore, CHAR always consumes the same amount of storage.<\/li><li>VARCHAR columns store variable-length strings up to 65,535 characters. You specify the maximum length that you intend to store when you create the table. VARCHAR columns are not padded, which means they consume as much space as the length of the string (plus one or two bytes for a prefix that indicates the string\u2019s length).<\/li><\/ul>\n\n\n\n<p>CHARs are great for storing short strings when you know how long they are. You can also use VARCHAR to store short strings\u2014VARCHAR(40), for example\u2014but it can store any string up to the maximum column size, using a variable amount of storage space.<\/p>\n\n\n\n<p>Next, TEXT and its variants. Text is based on the BLOB (binary large object) type. These are variable-length data types, and they come in three main variants:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>TEXT has a maximum length of 65,535 bytes\u2014the same as VARCHAR.<\/li><li>MEDIUMTEXT has a maximum length of about 16 megabytes.<\/li><li>LONGTEXT has a maximum length of about 4 gigabytes.<\/li><\/ul>\n\n\n\n<p>In many respects, TEXT is similar to VARCHAR: it stores variable-length strings up to 65,535 bytes. For both, the amount of space consumed depends on the length of the string. However, there are some key differences:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>TEXT columns can only be indexed for a specified length. MySQL doesn\u2019t index the full length of TEXT data types (except for <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/fulltext-search.html\" target=\"_blank\" rel=\"noreferrer noopener\">full-text search indexes<\/a>).<\/li><li>The full length of the string can\u2019t be used for sorting.<\/li><li>Queries involving TEXT columns can lead to the creation of a temporary table on disk instead of in memory. This is because MySQL\u2019s memory storage engine does not support TEXT data types. There is a significant performance penalty to using disk-based tables, which means queries using TEXT can be much slower.<\/li><li>TEXT data may be stored off the table, with a pointer to the string stored on the table. Accessing data stored in this way is slower. VARCHAR data is always stored on the table. If data is frequently retrieved, inline storage offers faster performance.<\/li><\/ul>\n\n\n\n<p>Taken together, these qualities of TEXT mean that, for many circumstances involving a web application, it\u2019s preferable to use VARTEXT when storing variable-length text of fewer than 65,535 characters.<\/p>\n\n\n\n<p>None of this means that you <em>must<\/em> use VARCHAR. TEXT is a reasonable choice if the limitations we\u2019ve described are unimportant to you. However, there are circumstances where TEXT has undesirable consequences that don\u2019t arise if you use VARCHAR.<\/p>\n\n\n\n<p>If you want to store longer variable-length strings, MEDIUMTEXT and LONGTEXT are your best option. However, you may run into network and other system limitations if you try to insert large amounts of data. Throwing multi-gigabyte BLOBs into your database is rarely wise, so be sure to check the data\u2019s size before storing it.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>VARCHAR in Action<\/strong><\/h2>\n\n\n\n<p>To see a real-life example of how these MySQL data types are used, let\u2019s explore a couple of tables in WordPress\u2019s database with cPanel\u2019s built-in phpMyAdmin tools. You can examine the data types of any MySQL database on your server by opening phpMySQL, locating the database in the left-hand column, and clicking the <em>Structure<\/em> link adjacent to the relevant table.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.cpanel.com\/wp-content\/uploads\/2021\/07\/00-mysql-varchar-comments.png\" alt=\"\" class=\"wp-image-61817\"\/><\/figure>\n\n\n\n<p>If you look at the <em>comments<\/em> table, you\u2019ll see that WordPress\u2019s developers have used VARCHAR for shorter text fields: comment type, comment agent, comment author, URL, and so on. But they have chosen to use a TEXT field for the comment content, a column unlikely to be used in an index.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/blog.cpanel.com\/wp-content\/uploads\/2021\/07\/01-mysql-text-data-type-post.png\" alt=\"\" class=\"wp-image-61825\"\/><\/figure>\n\n\n\n<p>In the post table, a similar pattern is evident. VARCHAR for post status, post password, and post name. The post content column, which is likely to exceed VARCHAR\u2019s space limit, is stored in a LONGTEXT column with a theoretical maximum of 4 GB.<\/p>\n\n\n\n<p>PhpMySQL is just one of the many web hosting, database management, and automation tools built into cPanel &amp; WHM. You can read more about managing database with cPanel in:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/blog.cpanel.com\/how-to-reset-the-mysql-root-password-and-mysql-user-passwords\/\" target=\"_blank\" rel=\"noreferrer noopener\">How To Reset the MySQL\u00ae Root Password And MySQL User Passwords<\/a><\/li><li><a href=\"https:\/\/blog.cpanel.com\/how-to-use-a-remote-mysql-database-with-cpanel\/\" target=\"_blank\" rel=\"noreferrer noopener\">How To Use A Remote MySQL\u00ae Database With cPanel<\/a><\/li><li><a href=\"https:\/\/blog.cpanel.com\/how-to-back-up-and-restore-mysql-databases-in-cpanel\/\" target=\"_blank\" rel=\"noreferrer noopener\">How to Back Up and Restore MySQL\u00ae Databases in cPanel<\/a><\/li><\/ul>\n\n\n\n<p>As always, if you have any feedback or comments, please let us know. We are here to help in the best ways we can. You\u2019ll find us on <a href=\"https:\/\/go.cpanel.net\/discord\" target=\"_blank\" rel=\"noreferrer noopener\">Discord<\/a>, the <a href=\"https:\/\/forums.cpanel.net\/\" target=\"_blank\" rel=\"noreferrer noopener\">cPanel forums<\/a>, and <a href=\"https:\/\/reddit.com\/r\/cpanel\" target=\"_blank\" rel=\"noreferrer noopener\">Reddit<\/a>. Be sure to also follow us on&nbsp;<a href=\"https:\/\/facebook.com\/cpanel\" target=\"_blank\" rel=\"noreferrer noopener\">Facebook<\/a>,&nbsp;<a href=\"https:\/\/instagram.com\/cpanel\" target=\"_blank\" rel=\"noreferrer noopener\">Instagram<\/a>, and&nbsp;<a href=\"https:\/\/twitter.com\/cpanel\" target=\"_blank\" rel=\"noreferrer noopener\">Twitter<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you\u2019re building a database for a web application, one of the most impactful decisions is the data type you choose for text data fields. MySQL provides multiple string data types, each with unique characteristics and trade-offs. The data type you choose affects how much data you can store, where it\u2019s stored, the functionality available [&hellip;]<\/p>\n","protected":false},"author":77,"featured_media":65829,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[49],"tags":[],"class_list":["post-61809","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-products"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>VARCHAR vs. TEXT for MySQL Databases | cPanel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VARCHAR vs. TEXT for MySQL Databases | cPanel\" \/>\n<meta property=\"og:description\" content=\"When you\u2019re building a database for a web application, one of the most impactful decisions is the data type you choose for text data fields. MySQL provides multiple string data types, each with unique characteristics and trade-offs. The data type you choose affects how much data you can store, where it\u2019s stored, the functionality available [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/\" \/>\n<meta property=\"og:site_name\" content=\"cPanel\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/cpanel\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-10-07T13:58:15+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/devel.www.cpanel.net\/wp-content\/uploads\/2021\/07\/VARCHAR-vs-TEXT.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"475\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"cPanel Community\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cPanel\" \/>\n<meta name=\"twitter:site\" content=\"@cPanel\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"cPanel Community\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/\",\"url\":\"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/\",\"name\":\"VARCHAR vs. TEXT for MySQL Databases | cPanel\",\"isPartOf\":{\"@id\":\"https:\/\/devel.www.cpanel.net\/#website\"},\"datePublished\":\"2021-10-07T13:58:15+00:00\",\"dateModified\":\"2021-10-07T13:58:15+00:00\",\"author\":{\"@id\":\"https:\/\/devel.www.cpanel.net\/#\/schema\/person\/8cf97408aad4fb70cf55d11a1d4f57f8\"},\"breadcrumb\":{\"@id\":\"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/devel.www.cpanel.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VARCHAR vs. TEXT for MySQL Databases\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/devel.www.cpanel.net\/#website\",\"url\":\"https:\/\/devel.www.cpanel.net\/\",\"name\":\"cPanel\",\"description\":\"Hosting Platform of Choices\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/devel.www.cpanel.net\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/devel.www.cpanel.net\/#\/schema\/person\/8cf97408aad4fb70cf55d11a1d4f57f8\",\"name\":\"cPanel Community\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/devel.www.cpanel.net\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e1949945083b5526bb95711bd3d616b3?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e1949945083b5526bb95711bd3d616b3?s=96&d=mm&r=g\",\"caption\":\"cPanel Community\"},\"description\":\"The web hosting industry's most reliable management solution since 1997. With our first-class support and rich feature set, it's easy to see why our customers and partners make cPanel &amp; WHM their hosting platform of choice. For more information, visit cPanel.net.\",\"sameAs\":[\"https:\/\/cpanel.net\"],\"url\":\"https:\/\/devel.www.cpanel.net\/blog\/author\/cpadmin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"VARCHAR vs. TEXT for MySQL Databases | cPanel","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:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/","og_locale":"en_US","og_type":"article","og_title":"VARCHAR vs. TEXT for MySQL Databases | cPanel","og_description":"When you\u2019re building a database for a web application, one of the most impactful decisions is the data type you choose for text data fields. MySQL provides multiple string data types, each with unique characteristics and trade-offs. The data type you choose affects how much data you can store, where it\u2019s stored, the functionality available [&hellip;]","og_url":"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/","og_site_name":"cPanel","article_publisher":"https:\/\/www.facebook.com\/cpanel\/","article_published_time":"2021-10-07T13:58:15+00:00","og_image":[{"width":1200,"height":475,"url":"https:\/\/devel.www.cpanel.net\/wp-content\/uploads\/2021\/07\/VARCHAR-vs-TEXT.png","type":"image\/png"}],"author":"cPanel Community","twitter_card":"summary_large_image","twitter_creator":"@cPanel","twitter_site":"@cPanel","twitter_misc":{"Written by":"cPanel Community","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/","url":"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/","name":"VARCHAR vs. TEXT for MySQL Databases | cPanel","isPartOf":{"@id":"https:\/\/devel.www.cpanel.net\/#website"},"datePublished":"2021-10-07T13:58:15+00:00","dateModified":"2021-10-07T13:58:15+00:00","author":{"@id":"https:\/\/devel.www.cpanel.net\/#\/schema\/person\/8cf97408aad4fb70cf55d11a1d4f57f8"},"breadcrumb":{"@id":"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/devel.www.cpanel.net\/blog\/products\/varchar-vs-text-for-mysql-databases\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/devel.www.cpanel.net\/"},{"@type":"ListItem","position":2,"name":"VARCHAR vs. TEXT for MySQL Databases"}]},{"@type":"WebSite","@id":"https:\/\/devel.www.cpanel.net\/#website","url":"https:\/\/devel.www.cpanel.net\/","name":"cPanel","description":"Hosting Platform of Choices","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/devel.www.cpanel.net\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/devel.www.cpanel.net\/#\/schema\/person\/8cf97408aad4fb70cf55d11a1d4f57f8","name":"cPanel Community","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/devel.www.cpanel.net\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/e1949945083b5526bb95711bd3d616b3?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e1949945083b5526bb95711bd3d616b3?s=96&d=mm&r=g","caption":"cPanel Community"},"description":"The web hosting industry's most reliable management solution since 1997. With our first-class support and rich feature set, it's easy to see why our customers and partners make cPanel &amp; WHM their hosting platform of choice. For more information, visit cPanel.net.","sameAs":["https:\/\/cpanel.net"],"url":"https:\/\/devel.www.cpanel.net\/blog\/author\/cpadmin\/"}]}},"_links":{"self":[{"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/posts\/61809"}],"collection":[{"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/users\/77"}],"replies":[{"embeddable":true,"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/comments?post=61809"}],"version-history":[{"count":0,"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/posts\/61809\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/media\/65829"}],"wp:attachment":[{"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/media?parent=61809"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/categories?post=61809"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devel.www.cpanel.net\/wp-json\/wp\/v2\/tags?post=61809"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}