Menu
I want to insert a file in MYSQL database residing on a remote webserver using a webservice.
My question is: What type of table column (e.g. varchar, etc.) will store a file? And will the insert statement be somewhat different in case of a file?
978
Add File Uploads CREATE TABLE uploads (id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY, description CHAR(50), data LONGBLOB, filename CHAR(50), filesize CHAR(50), filetype CHAR(50) ); The first thing you should notice is a field called id that is set to AUTO_INCREMENT.
In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases, right-click the database from which to add the files, and then click Properties. In the Database Properties dialog box, select the Files page. To add a data or transaction log file, click Add.
In general, the contents of a file are stored under Clob (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) datatype in MySQL database. JDBC provides support for the Clob datatype, to store the contents of a file in to a table in a database.
mysql> LOAD DATA LOCAL INFILE '/path/pet. txt' INTO TABLE pet; If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead: mysql> LOAD DATA LOCAL INFILE '/path/pet.
The BLOB datatype is best for storing files.
109
File size by MySQL type:
Yet, in most cases, I would NOT recommend storing big blobs of bytes in database, even if it supports it, because it will increase overall database size & may cause real performance issues. You can read more on topic here. Many databases that care about consistent performance won't even let you do such thing. Like e.g. AWS DynamoDB, which is known to perform extremely well at any scale, limits single item record to 400KB. MongoDB does allow 16MB, which is also already too much, imo. MySQL allows all 4GB if you wish. But again, think twice before doing that. The case where you may be OK to store big blob of data with these column types would be - you have small traffic database and you just want to save all the stuff in one place for faster development. Like internal system in a small company.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
