Learn more. What is difference between storing data in a blob, vs. Ask Question. Asked 8 years, 11 months ago. Active 8 years, 11 months ago. Viewed 37k times. I have a question about the blob data type in MySQL. Improve this question. SaidbakR Add a comment. Active Oldest Votes. I also read that an alternative is to store the file on disk and include a pointer to its location in the database Bearing in mind all above considerations a common practice for web applications is to store your files elsewhere than your MySQL and then simply store it's path on your database.
But in abstract, on MySQL 5 and forward the blob is stored as following: Innodb stores either whole blob on the row page or only 20 bytes BLOB pointer giving preference to smaller columns to be stored on the page, which is reasonable as you can store more of them. I hope it helped. Improve this answer. Community Bot 1 1 1 silver badge.
Bruno Vieira Bruno Vieira 3, 1 1 gold badge 22 22 silver badges 35 35 bronze badges. If you store data is BLOB field, you are making it part of your object abstraction.
Here are a few other factors that may affect your decision: Blobs are stored logically with a row. Bill Karwin Bill Karwin k 81 81 gold badges silver badges bronze badges. SaidbakR SaidbakR And supposes he changes from windows server to linux. Still a better approach to point to files? In addition if it is a web application, commonly the relative paths are stored.
Backing up a database with tens or hundreds of GB of files embedded in it is not fun at all. Backing up a filesystem with several TB of data on it is easy with rsync. And besides that, folders and layouts changes across OSes making that a hard process when it would simply being solved with a mysqldump — Bruno Vieira.
Hi, thank you for the responses. But I'm still confused on one aspect. If the blob contains a pointer to the location of a file, how is that any different than using a varchar to store a pointer to the file on disk? Sorry if my question is unclear, I'm not too familiar with the hardware side of things.
Show 2 more comments. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Each storage engine uses a different method to handle the data of such datatype. There are certain things that you should know about the BLOB datatype and are listed below —.
We will create a table named gallery that will contain the id, name, and image columns. The image column will be of BLOB datatype. We can use the following query to create our table —.
We will see a program in PHP which will show how we can insert and retrieve the blob values to and from database which is as follows —. The output of the above code when the file is saved with name educba. Let us retrieve the record in MySQL and check the contents of the gallery table —.
When we try to retrieve the blob value by using the following query statement the output is too large due to the jpg file stored in the image column of blob type which makes resultset vast and unreadable on the terminal because jpg file is not parsable on the terminal.
The blob type is used in MySQL to store media files like images, pdf files, videos, reports, etc. The precautions to be taken while using this type as discussed above. Numeric Type Attributes. Out-of-Range and Overflow Handling. Date and Time Data Type Syntax. Fractional Seconds in Time Values. Conversion Between Date and Time Types. The Geometry Class Hierarchy. GeometryCollection Class. MultiLineString Class. Supported Spatial Data Formats.
Geometry Well-Formedness and Validity. Spatial Reference System Support. Creating Spatial Columns. Populating Spatial Columns. Optimizing Spatial Analysis. Creating Spatial Indexes.
0コメント