How to insert a binary stream in to MySQL without ASCII conversation?

437 views Asked by At

I have an application written in "C" - debian distro (using libmysqlclient). My application is inserting a huge number of rows in to the database (around 30.000 rows/sec, row length ~ 150 B). Insertion takes many of CPU, because the client (my app) must convert binary stream (integers, blobs) in to ASCII representation in to a valid SQL insert statement. And also the MySQL must convert this values in to binary representation and store it to the file.

And my question. Is some possibility to call SQL insert without this conversation? I have been using a classical sprintf (implemented in libc). Now, I'm using optimized version and without calling sprintf (too many func calling). I thought I'll use a MySQL prepared statement, but it seems a prepared statement also convert '?' variables to the ASCII.

Jan

1

There are 1 answers

1
pmg On

See mysql_real_query().

22.8.7.56 mysql_real_query()

int mysql_real_query(MYSQL *mysql, const char *stmt_str, unsigned long length)

Apparently, something like the below should work

#define DATA "insert into atable (acolumn) values('zero\000quotes\"\'etc ...')"
if (mysql_real_query(db, DATA, sizeof DATA - 1)) /* error */;