0

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?

  • When you store the BLOB, you throw away a lot of features the relational storage offers - you cannot search inside the blob using sql queries, you cannot retrieve only a part of the blob etc. It may or may not be a problem depending on your use case. About he NaN - did you consider using NULL instead? Or adding one more column of tinyint(1) to work as a flag is_nan. – jkavalik Sep 14 '15 at 19:12
  • Those issues with the BLOB should not be a problem with my use cases. I am only returning the data and comparing the entire array for equality. I may actually consider the JSON type when MySQL 5.7 is released though. I did consider adding another column as a flag, but I was also drawn to the ability to remove the different tables for each type. It means that I don't have to group_concat the rows of each array element and join the tables for each type when I return the data to the user. – Patrick Sep 14 '15 at 19:24
  • And do you actually need the database at all? There are probably better tools for a blob storage. – jkavalik Sep 14 '15 at 19:39
  • The database has been quite useful. I'm storing time series data for thousands of channels with queries to find the values of a channel at a given time, compare the values of channels at two different times and find changes in the values of channels between times. Using transactions also ensures against partial writes. It may be a bit late in the project to change, but I'm open to other suggestions. What other approaches might you recommend? – Patrick Sep 14 '15 at 19:54
1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.