How to store text and pictures in MySQL

This article shows you how to store text and pictures in MySQL. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Large text data types in Oracle

Clob long text type (not supported in MySQL, using text) Blob binary type

MySQL database

Text long text type TINYTEXT: 256bytes TEXT: 65535 bytes = > ~ 64kb MEDIUMTEXT: 16777215 bytes = > ~ 16MB LONGTEXT: 4294967295 bytes = > ~ 4GBBlob binary type

For example:

Build a table

CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, content LONGTEXT,-- text field img LONGBLOB-- picture field)

The text is stored as a character type, and the picture is stored as a binary type. The specific method of setting parameters is different from the method of obtaining data.

For example:

/ / when storing text / / when storing text, set the parameter to character stream FileReader readerpstmt.setCharacterStream (1, reader); / / when getting parameters / / mode 1:Reader r = rs.getCharacterStream ("content"); / / get long text data, mode 2:System.out.print ("content"); / / set parameter to binary stream InputStream in pstmt.setBinaryStream (1, in) when storing binary images / / get binary stream InputStream in = rs.getAsciiStream ("img"); / * Save photos * * / @ Testpublic void test2 () {String sql = "insert into test (img) values (?)"; try {con = JDBCUtil.getConnection (); pstmt = con.prepareStatement (sql); / / set parameters / / get text File file = new File ("f:/a.jpg"); InputStream in = new FileInputStream (file) / / set the parameter to binary stream pstmt.setBinaryStream (1, in); / / execute sql pstmt.executeUpdate (); in.close ();} catch (Exception e) {e.printStackTrace ();} finally {try {JDBCUtil.close (con, pstmt);} catch (Exception e) {/ / TODO Auto-generated catch block e.printStackTrace () } / * get photos * * / @ Testpublic void test3 () {String sql = "select * from test where id=?;"; try {con = JDBCUtil.getConnection (); pstmt = con.prepareStatement (sql); / / set parameter pstmt.setInt (1,2); / / execute query rs = pstmt.executeQuery (); while (rs.next ()) {byte [] buff = new byte [1024] InputStream in = rs.getAsciiStream ("img"); int lumped 0; OutputStream out = new FileOutputStream (new File ("f:/1.jpg")); while ((l=in.read (buff))! =-1) {out.write (buff, 0, l);} in.close (); out.close ();}} catch (Exception e) {e.printStackTrace () } finally {try {JDBCUtil.close (con, pstmt);} catch (Exception e) {/ / TODO Auto-generated catch block e.printStackTrace ();} this is how to store text and pictures in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.