EXAMPLE: How to store pictures in Mysql database -- Mysql 存储图片方法 官方论坛
Posted by: Michael G. Zinner
Date: May 30, 2005 12:05PM
1) Download and install MySQL Query Browser
2) Start it and create a connection to your database. Use the "test" as default schema.
3a) Excute the following SQL statement.
CREATE TABLE `test`.`pic` (
`idpic` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`caption` VARCHAR(45) NOT NULL,
`img` LONGBLOB NOT NULL,
PRIMARY KEY(`idpic`)
)
TYPE = InnoDB;
3b) Instead of creating the table via SQL, you can create a table by pressing the right mouse button on the schemata tree and select "Create New Table"
Type "pic" in the Table Name field. Press enter. Press enter again to create the first column named "idpic" (that will be your primary key). Press enter to accept INTEGER as datatype. Enter "caption" to store a name for you picture. Press enter. Type "v" (which will trigger VARCHAR(45)) and press enter. Type "img" and enter. Type "longb" (which will trigger LONGBLOB) and press enter. Click [Apply Changes]. This will show you the SQL statement that will be executed.
Press [Execute] to create the table.
5) Close the table editor or refresh the schemata tree and doubleclick the new generated table in the schemata tree
6) This will generate the SQL statement
SELECT * FROM pic p
Now click the table again to execute it. You can press Ctrl+Enter instead (of course)
7) Now click on the [Edit] button in the action bar at the bottom. Doubleclick the empty caption field. Enter a name for your picture. Press enter.
8) Click the "Open" overlay icon in the left of the img field in the first row. Or you can press the right mousebutton on the field and select "Load Field Content" from the popup menu.
9) Click [Apply Changes] to actually store the new data in the database.
10) Now click the "Magnifyer" overlay icon and if you have inserted a JPG, PNG or BMP image you will see it in the field viewer.
Tip: To be able to insert images that are bigger than 1MB you have to increase the max_allowed_packed option in the server configuration file. You can change that in the Startup Variables section in the MySQL Administrator on the Networking page.
Michael Zinner, Team Lead, Developer Tools
MySQL AB, www.mysql.com
2) Start it and create a connection to your database. Use the "test" as default schema.
3a) Excute the following SQL statement.
CREATE TABLE `test`.`pic` (
`idpic` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`caption` VARCHAR(45) NOT NULL,
`img` LONGBLOB NOT NULL,
PRIMARY KEY(`idpic`)
)
TYPE = InnoDB;
3b) Instead of creating the table via SQL, you can create a table by pressing the right mouse button on the schemata tree and select "Create New Table"
Type "pic" in the Table Name field. Press enter. Press enter again to create the first column named "idpic" (that will be your primary key). Press enter to accept INTEGER as datatype. Enter "caption" to store a name for you picture. Press enter. Type "v" (which will trigger VARCHAR(45)) and press enter. Type "img" and enter. Type "longb" (which will trigger LONGBLOB) and press enter. Click [Apply Changes]. This will show you the SQL statement that will be executed.
Press [Execute] to create the table.
5) Close the table editor or refresh the schemata tree and doubleclick the new generated table in the schemata tree
6) This will generate the SQL statement
SELECT * FROM pic p
Now click the table again to execute it. You can press Ctrl+Enter instead (of course)
7) Now click on the [Edit] button in the action bar at the bottom. Doubleclick the empty caption field. Enter a name for your picture. Press enter.
8) Click the "Open" overlay icon in the left of the img field in the first row. Or you can press the right mousebutton on the field and select "Load Field Content" from the popup menu.
9) Click [Apply Changes] to actually store the new data in the database.
10) Now click the "Magnifyer" overlay icon and if you have inserted a JPG, PNG or BMP image you will see it in the field viewer.
Tip: To be able to insert images that are bigger than 1MB you have to increase the max_allowed_packed option in the server configuration file. You can change that in the Startup Variables section in the MySQL Administrator on the Networking page.
Michael Zinner, Team Lead, Developer Tools
MySQL AB, www.mysql.com
#########################################
下面是国内各论坛中的讨论
1.写入时并无格式之分，只是保存二进制数据，读取后和写入时的格式一样。
jpg图片用二进制格式写入mysql，再以二进制格式读取，图片格式为jpg
gif图片用二进制格式写入mysql，再以二进制格式读取，图片格式为gif
...就是说与图片的格式无关。
2.图片用数据库保存，以文件方式保存这两种方式各有利弊。
3.mysql保存图片用BLOB类型。
BLOB类型按可存储数据的大小分几种(
在Mysql里, BLOB类型，最大长度64K，恐怕不太适合存储大一点的图像。
可以使用MEDIUMBLOB(最大16M）或者LONGBLOB类型(4G) )。
总的来说，绝大多数人认为将图片储存到服务器上，然后再把对应的路径和名字储存到数据库中的方案更合理。
