mysql 存pdf文件,在MySQL中存储PDF文件

How would you store a PDF document in a field in MySQL?

Currently I have a list of customers and each customer has a certificate with information about their account that they can give to other companies to prove that they're our customer. Currently their certificate is exported as a PDF and e-mailed to someone here at work (the customer gets a physical copy as well), and that person's mailbox is filled with these e-mails. I'd much prefer to just have it in the customer's record - allowing it to be accessed via the customer's file in our in-house CRM.

I considered putting the PDFs in a folder and storing their location as a varchar in the customer's record, but if the PDFs get moved/deleted/etc. then we're up a creek.

My understanding is that a BLOB or MEDIUMBLOB is the type of field that I'd use to store it, but I'm a little ignorant in this regard. I'm not sure how to store something like that in the field (what C# datatype to give it), and then how to get it and open it via a PDF reader.

解决方案

Put it in the database, but the BLOB datatype probably won't cut it. The MEDIUMBLOB is normally sufficient.

BLOB, TEXT L + 2 bytes, where L < 216

MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 224

LONGBLOB, LONGTEXT L + 4 bytes, where L < 232

I've used this several times with very good results. Be sure to save the filesize too, as it makes it easier to retrieve it. Not sure if it applies to C# as it does to PHP.

If using prepared statements with parameters the data will automatically be escaped AFAIK.

Also I can see no real reason as to why the database itself would get slow when storing this type of data in it. The main bottleneck will of course be the transfer of the data. Also MySQL is sometimes restrictive about the maximum length of queries and the responses in particular.

Once you have it running, it's pretty neat, especially when dealing with lots of small files. For a small number of large files, this approach does not make sense, better use some backup system to deal with moved/deleted files.