I am trying to store variable length arrays of C++ doubles into a MySQL database.
I was initially storing each value in the array as a row of the form:
`pv_name` varchar(60) NOT NULL,
`time_stamp` bigint(20) UNSIGNED NOT NULL,
`array_index` bigint(20) UNSIGNED NOT NULL,
`value` double NOT NULL,
`alarm_status_id` tinyint(3) UNSIGNED NOT NULL,
`alarm_severity_id` tinyint(3) UNSIGNED NOT NULL
The pv_name and time_stamp uniquely identified each array. The array_index identified each element of the array.
The problem that I ran into is that the MySQL double type does not accept a 'nan' value. So my thought was to use a single blob type for the entire array. Would this be recommended? One advantage of doing so is that it allows me to store values of other types (tinyint, smallint, int, float, varchar) in this blob column in the same table, whereas before I needed a different table for each type. If this is appropriate, what is the best way to insert the array into the blob? I am currently casting it to a string using a format string of '%.17f' for each value and then using setString with a prepared statement (from the MySQL C++ connector (libmysqlcppconn)). Is it possible to use the '%g' format string instead without losing information? Is there an altogether different approach that may be more appropriate?
Best Answer
Plan A: Use NULL to indicate NaN.
Plan B: Use JSON now. Build whatever structure makes sense in you app, encode it in JSON, store it in a TEXT field. Note: JSON is expecting utf8 string inside. Keep in mind that you need to fetch the entire JSON field and manipulate it in your app, not in SQL.