{"id":13845,"date":"2023-12-29T19:07:57","date_gmt":"2023-12-30T02:07:57","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13845"},"modified":"2023-12-29T19:07:58","modified_gmt":"2023-12-30T02:07:58","slug":"mysql-blob","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/","title":{"rendered":"MySQL BLOB"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn about MySQL BLOB data type and its applications.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to MySQL BLOB data type<\/h2>\n\n\n\n<p>In MySQL, a BLOB (Binary Large Object) is a data type that allows you to store large binary data, such as images, audio, video, and so on. BLOBs are useful when you want to store and retrieve data in your database. <\/p>\n\n\n\n<p>MySQL supports the following types of BLOBs:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>TINYBLOB<\/strong>: Maximum length of 255 bytes.<\/li>\n\n\n\n<li><strong>BLOB<\/strong>: Maximum length of 65,535 bytes.<\/li>\n\n\n\n<li><strong>MEDIUMBLOB<\/strong>: Maximum length of 16,777,215 bytes.<\/li>\n\n\n\n<li><strong>LONGBLOB<\/strong>: Maximum length of 4,294,967,295 bytes.<\/li>\n<\/ul>\n\n\n\n<p>When you create tables that store BLOBs, you select the appropriate BLOB type based on the size of binary data you plan to store.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">MySQL BLOB data type example<\/h2>\n\n\n\n<p>First, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-create-table\/\">create a table<\/a> that includes a <code>BLOB<\/code> column:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">CREATE TABLE images (\r\n   id INT PRIMARY KEY AUTO_INCREMENT,\n   title VARCHAR(<span class=\"hljs-number\">255<\/span>) NOT <span class=\"hljs-keyword\">NULL<\/span>,\r\n   image_data LONGBLOB NOT <span class=\"hljs-keyword\">NULL<\/span>\r\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, show the value of the <code>secure_file_priv<\/code> variable:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"CSS\" data-shcb-language-slug=\"css\"><span><code class=\"hljs language-css\"><span class=\"hljs-selector-tag\">SELECT<\/span> @<span class=\"hljs-keyword\">@secure_file_priv<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">CSS<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">css<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">+------------------------------------------------+\r\n| @@secure_file_priv                             |\r\n+------------------------------------------------+\r\n| C:\\ProgramData\\MySQL\\MySQL Server <span class=\"hljs-number\">8.0<\/span>\\Uploads\\ |\r\n+------------------------------------------------+\r\n<span class=\"hljs-number\">1<\/span> row <span class=\"hljs-keyword\">in<\/span> <span class=\"hljs-keyword\">set<\/span> (0.00 sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code>secure_file_priv<\/code> system variable restricts the locations on the MySQL server from which the <code>LOAD_FILE()<\/code> function can read files.<\/p>\n\n\n\n<p>If you attempt to load the file from other locations, the <code>LOAD_FILE()<\/code> function returns NULL.<\/p>\n\n\n\n<p>Third, insert a binary image into the <code>image_data<\/code> of the <code>images<\/code> table using the <code>LOAD_FILE()<\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">INSERT INTO images (title,image_data) \nVALUES (<span class=\"hljs-string\">'MySQL tutorial'<\/span>, LOAD_FILE(<span class=\"hljs-string\">'C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads\/logo.png'<\/span>));<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Make sure to replace <code>C:\/ProgramData\/MySQL\/MySQL Server 8.0\/Uploads\/logo.png<\/code> with the actual absolute path to your image file.<\/p>\n\n\n\n<p class=\"note\">Notice that you need to replace the backslash (<code>\\<\/code>) with the forward-slash (<code>\/<\/code>) in the path to the file on Windows to make it work properly.<\/p>\n\n\n\n<p>Finally, retrieve the binary data from the <code>BLOB<\/code> column:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT * FROM images;<\/code><\/span><\/pre>\n\n\n<p>In practice, you often use applications written in Python, PHP, Java, and so on to read files in the client and  store them in the <code>BLOB<\/code> column:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.mysqltutorial.org\/php-mysql\/php-mysql-blob\/\">PHP BLOB<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/python-mysql\/python-mysql-blob\/\">Python BLOB<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-jdbc-tutorial\/writing-and-reading-mysql-blob-using-jdbc\/\">Java BLOB<\/a><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use MySQL BLOB to store large binary data in the database.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful? <\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"13845\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/\"\n\t\t\t\tdata-post-title=\"MySQL BLOB\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"13845\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/\"\n\t\t\t\tdata-post-title=\"MySQL BLOB\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you will learn about MySQL BLOB data type and its applications.<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":174,"menu_order":80,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-13845","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL BLOB<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will learn about MySQL BLOB data type and its applications.\" \/>\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.mysqltutorial.org\/mysql-basics\/mysql-blob\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL BLOB\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will learn about MySQL BLOB data type and its applications.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-30T02:07:58+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-blob\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-blob\\\/\",\"name\":\"MySQL BLOB\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"datePublished\":\"2023-12-30T02:07:57+00:00\",\"dateModified\":\"2023-12-30T02:07:58+00:00\",\"description\":\"In this tutorial, you will learn about MySQL BLOB data type and its applications.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-blob\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-blob\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/mysql-blob\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Basics\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-basics\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MySQL BLOB\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL BLOB","description":"In this tutorial, you will learn about MySQL BLOB data type and its applications.","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.mysqltutorial.org\/mysql-basics\/mysql-blob\/","og_locale":"en_US","og_type":"article","og_title":"MySQL BLOB","og_description":"In this tutorial, you will learn about MySQL BLOB data type and its applications.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/","og_site_name":"MySQL Tutorial","article_modified_time":"2023-12-30T02:07:58+00:00","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/","url":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/","name":"MySQL BLOB","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"datePublished":"2023-12-30T02:07:57+00:00","dateModified":"2023-12-30T02:07:58+00:00","description":"In this tutorial, you will learn about MySQL BLOB data type and its applications.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-blob\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Basics","item":"https:\/\/www.mysqltutorial.org\/mysql-basics\/"},{"@type":"ListItem","position":3,"name":"MySQL BLOB"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13845","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=13845"}],"version-history":[{"count":2,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13845\/revisions"}],"predecessor-version":[{"id":13847,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13845\/revisions\/13847"}],"up":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/174"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=13845"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}