Fields - 1.82.0

field_view vs field

This library implements two types representing the concept of a field: field_view, which is non-owning, and field, which is owning. The relationship between them is similar to the one between std::string_view and std::string.

For efficiency reasons, all library functions return field_views. For example:

results result;
conn.query("SELECT 'Hello world!'", result);

// fv doesn't own its memory; if result goes out of scope, fv becomes invalid
field_view fv = result.rows().at(0).at(0);

// sv also points into result; if result goes out of scope, sv becomes invalid
string_view sv = fv.as_string();

When dealing with scalars (anything that is neither a string nor a blob), field_view's accessors make a copy of the scalar:

results result;
conn.query("SELECT 42", result);

// fv doesn't own its memory; if result goes out of scope, fv becomes invalid
field_view fv = result.rows().at(0).at(0);

// intv is valid even after result goes out of scope
std::int64_t intv = fv.as_int64();

field_views are cheap to create and to copy, as they are small objects and don't perform any memory allocations. They are also immutable. On the other hand, fields may be more expensive to create and copy, as they may perform memory allocations.

You may create a field from a field_view, taking ownership of its contents:

results result;
conn.query("SELECT 'Hello world!'", result);

// fv doesn't own its memory; if result goes out of scope, fv becomes invalid
field_view fv = result.rows().at(0).at(0);

// f takes ownership of fv's contents. f is valid even after result goes out of scope
field f(fv);

field and field_view use the same underlying types for scalars. For strings and blobs, field uses the owning types std::string and blob, while field_view uses the reference types string_view and blob_view.

field accessors return references, which allow you to mutate the underlying object:

field f("my_string");            // constructs a field that owns the string "my_string"
std::string& s = f.as_string();  // s points into f's storage
s.push_back('2');                // f now holds "my_string2"

You can also mutate a field using the assignment operator. This allows you to also change the underlying type of a field:

field f("my_string");  // constructs a field that owns the string "my_string"
f = 42;                // destroys "my_string" and stores the value 42 as an int64

Arithmetic types

This library uses std::int64_t to represent all MySQL signed integers (TINYINT, SMALLINT, MEDIUMINT...). Similarly, unsigned integers use std::uint64_t, 32-bit floats use float and 64-bit doubles use double.

String types

String types (CHAR, VARCHAR, TEXT, SET...) are represented as string fields, which translates into string_view for field_view and std::string for field.

This library performs no character set conversion on strings. All strings are provided as the server sends them. If you've issued a "SET NAMES <charset-name>" statement, strings will be encoded according to <charset-name>. For details, see this section.

The JSON type is represented as a string containing the serialized JSON value. The DECIMAL type is also provided as a string, to avoid losing precision.

Blob types

The types BINARY, VARBINARY and BLOB (of any size) are represented as blob fields, which are similar to string fields, but represent binary data instead of characters. The following types are used:

blob_view is used by field_view. It's an alias for boost::span<const unsigned char> (a C++11 backport of std::span).
blob is used by field. It's an alias for std::vector<unsigned char>.

If MySQL adds any other type that this library doesn't understand, it is also represented as a blob.

The DATE type

MySQL DATE is represented as date in C++. date holds the year, month and day components of a date. It is a type close to the protocol, rather than a vocabulary type. The main reason for using date instead of a std::chrono::time_point type is that, under certain configurations, MySQL allows storing invalid dates, such as 2020-00-01. These are not representable as a std::chrono::time_point.

Unless dealing with these special values, we recommend converting dates to a time_point before using them. The member type date::time_point is a system-clock time_point that can represent the entire MySQL DATE range without problems. You can use date::as_time_point to perform the cast:

date d(2020, 2, 19);                      // d holds "2020-02-19"
date::time_point tp = d.as_time_point();  // now use tp normally

If the date is not valid, as_time_point will throw an exception.

You can query whether a date contains a valid date or not using date::valid:

date d1(2020, 2, 19);  // regular date
bool v1 = d1.valid();  // true
date d2(2020, 0, 19);  // invalid date
bool v2 = d2.valid();  // false

You can combine it with date::get_time_point, which performs an unchecked conversion:

date d = /* obtain a date somehow */ date(2020, 2, 29);
if (d.valid())
{
    // Same as as_time_point, but doesn't check for validity
    // Caution: be sure to check for validity.
    // If d is not valid, get_time_point results in undefined behavior
    date::time_point tp = d.get_time_point();

    // Use tp as required
    std::cout << tp.time_since_epoch().count() << std::endl;
}
else
{
    // the date is invalid
    std::cout << "Invalid date" << std::endl;
}

You can also construct a date from a time_point. If the time_point is out of range, an exception is thrown.

The DATETIME and TIMESTAMP types

Both types are represented as datetime in C++. datetime represents a broken time point, having year, month, day, hour, minute, second and microseconds.

MySQL also accepts invalid datetimes (like 2020-00-10 10:20:59.000000). The same considerations for date apply:

datetime dt1(2020, 10, 11, 10, 20, 59, 123456);  // regular datetime 2020-10-11 10:20:59.123456
bool v1 = dt1.valid();                           // true
datetime dt2(2020, 0, 11, 10, 20, 59);           // invalid datetime 2020-00-10 10:20:59.000000
bool v2 = dt2.valid();                           // false

datetime::time_point tp = dt1.as_time_point();  // convert to time_point

The datetime object doesn't carry any time zone information with it. The time zone semantics depend on the actual MySQL type:

DATETIME is a "naive" time point object. It represents a time point without any time zone information at all. It is up to the user to interpret which time zone the object is in.
When a TIMESTAMP object is inserted, it is interpreted to be in the connection's local time zone, as given by the time_zone variable, and converted to UTC for storage. When retrieved, it is converted back to the time zone indicated by time_zone. The retrieved value of a TIMESTAMP field is thus a time point in some local time zone, dictated by the current time_zone variable. As this variable can be changed programmatically from SQL, without the library knowing it, we represent TIMESTAMP's using the datetime object, which doesn't include time zone information.

When using TIMESTAMP, we recommend setting the time_zone to a known value. To illustrate this, consider an event-logging system with the following table definition:

results result;
conn.query(
    R"%(
        CREATE TEMPORARY TABLE events (
            id INT PRIMARY KEY AUTO_INCREMENT,
            t TIMESTAMP,
            contents VARCHAR(256)
        )
    )%",
    result
);

We will be inserting events with an explicit timestamp. We may also want to retrieve events with a timestamp filter. This is what our prepared statements would look like:

auto insert_stmt = conn.prepare_statement("INSERT INTO events (t, contents) VALUES (?, ?)");
auto select_stmt = conn.prepare_statement("SELECT id, t, contents FROM events WHERE t > ?");

These statements may be run from different parts of our code, or even from different applications. To get consistent results, we must make sure that the time zones used during insertion and retrieval are the same. By default, time_zone gets set to SYSTEM, which will use the server's time zone settings. This is not what we want here, so let's change it:

// This change has session scope. All operations after this query
// will now use UTC for TIMESTAMPs. Other sessions will not see the change.
// If you need to reconnect the connection, you need to run this again.
conn.query("SET @time_zone = 'UTC'", result);

With this, the insertion code can look like:

// Get the timestamp of the event. This may have been provided by an external system
// For the sake of example, we will use the current timestamp
datetime event_timestamp = datetime::now();

// event_timestamp will be interpreted as UTC if you have run SET @time_zone
conn.execute_statement(insert_stmt, std::make_tuple(event_timestamp, "Something happened"), result);

The querying code would be:

// Get the timestamp threshold from the user. We will use a constant for the sake of example
datetime threshold = datetime(2022, 1, 1);  // get events that happened after 2022-01-01

// threshold will be interpreted as UTC. The retrieved events will have their
// `t` column in UTC
conn.execute_statement(select_stmt, std::make_tuple(threshold), result);

If you don't set time_zone, you may apparently get the right results if you run both insertions and queries from clients that don't set time_zone and the server doesn't change its configuration. However, relying on this will make your applications brittle, so we don't recommend it.

The TIME type

The TIME type is a signed duration with a resolution of one microsecond. It is represented using the time type, an alias for a std::chrono::duration specialization with microseconds as period.

Field accessors

This table summarizes the available accesors for field_view and field.

MySQL to C++ type mappings

The following table reflects mapping from database types to C++ types. The range column shows the range of values that MySQL admits for that type. This library guarantees that any field retrieved from the database honors that range. The column_type column shows what metadata::type would return for a column of that type.

MySQL type	`field_kind`	C++ type	Range	`column_type`	Considerations
`TINYINT`	`int64`	`std::int64_t`	`-0x80` to `0x7f`	`tinyint`	1 byte integer
`TINYINT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` to `0xff`	`tinyint`	1 byte integer
`SMALLINT`	`int64`	`std::int64_t`	`-0x8000` to `0x7fff`	`smallint`	2 byte integer
`SMALLINT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` to `0xffff`	`smallint`	2 byte integer
`MEDIUMINT`	`int64`	`std::int64_t`	`-0x800000` to `0x7fffff`	`mediumint`	3 byte integer
`MEDIUMINT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` to `0xffffff`	`mediumint`	3 byte integer
`INT`	`int64`	`std::int64_t`	`-0x80000000` to `0x7fffffff`	`int_`	4 byte integer
`INT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` to `0xffffffff`	`int_`	4 byte integer
`BIGINT`	`int64`	`std::int64_t`	`-0x8000000000000000` to `0x7fffffffffffffff`	`bigint`	8 byte integer
`BIGINT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` and `0xffffffffffffffff`	`bigint`	8 byte integer
`YEAR`	`uint64`	`std::uint64_t`	[`1901`, `2155`], plus zero	`year`	1 byte integer type used to represent years Zero is often employed to represent invalid year values. We represent zero year as a numeric 0.
`BIT`	`uint64`	`std::uint64_t`	Depends on the bitset width. Max `0` to `0xffffffffffffffff`.	`bit`	A bitset between 1 and 64 bits wide.
`FLOAT`	`float_`	`float`	IEEE 754 `float` range	`float_`	4 byte floating point type
`DOUBLE`	`double_`	`double`	IEEE 754 `double` range	`double_`	8 byte floating point type
`DATE`	`date`	`date`	[`min_date`, `max_date`] (some MySQL implementations may allow a narrower range), plus invalid and zero dates (see `ALLOW_INVALID_DATES` and strict SQL mode).	`date`
`DATETIME`	`datetime`	`datetime`	[`min_datetime`, `max_datetime`] (some MySQL implementations may allow a narrower range), plus invalid and zero datetimes (see `ALLOW_INVALID_DATES` and strict SQL mode).	`datetime`	Time point type without time zone, with a resolution of one microsecond.
`TIMESTAMP`	`datetime`	`datetime`	[`min_datetime`, `max_datetime`] (the actual MySQL supported range is usually narrower, but we don't enforce it in the client), plus zero timestamps (see strict SQL mode).	`timestamp`	Time point type with a resolution of one microsecond.
`TIME`	`time`	`time`	[`min_time`, `max_time`]	`time`	Signed time duration, with a resolution of one microsecond.
`CHAR`	`string`	`string_view` or `std::string`		`char_`	Fixed-size character string.
`VARCHAR`	`string`	`string_view` or `std::string`		`varchar`	Variable size character string with a maximum size.
`TEXT` (all sizes)	`string`	`string_view` or `std::string`		`text`	Variable size character string.
`ENUM`	`string`	`string_view` or `std::string`		`enum_`	Character string with a fixed set of possible values (only one possible).
`SET`	`string`	`string_view` or `std::string`		`set`	Character string with a fixed set of possible values (many possible).
`JSON`	`string`	`string_view` or `std::string`		`json` (MySQL) or `text` (MariaDB)	A serialized JSON value of any type. Note that `metadata::type` is different depending on the DB system. MySQL has a dedicated `JSON` type, while in MariaDB `JSON` is an alias for `LONGTEXT`. JSON values are represented as strings by this library in both cases.
`DECIMAL`/`NUMERIC`	`string`	`string_view` or `std::string`	Depends on the column definition	`decimal`	A fixed precision numeric value. In this case, the string will contain the textual representation of the number (e.g. the string `"20.52"` for `20.52`).
`BINARY`	`blob`	`blob_view` or `blob`		`binary`	Fixed-size blob.
`VARBINARY`	`blob`	`blob_view` or `blob`		`varbinary`	Variable size blob with a maximum size.
`BLOB` (all sizes)	`blob`	`blob_view` or `blob`		`blob`	Variable size blob.
`GEOMETRY`	`blob`	`blob_view` or `blob`		`geometry`	Any of the spatial data types. The string contains the binary representation of the geometry type.

C++ to MySQL type mapping reference

This section shows how a parameter v in a expression conn.execute_statement(stmt, std::make_tuple(v), result) is interpreted by MySQL, depeding on v's type:

C++ type	MySQL type	Compatible with...
`signed char`, `short`, `int`, `long`, `long long`	`BIGINT`	Signed `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`
`unsigned char`, `unsigned short`, `unsigned int`, `unsigned long`, `unsigned long long`	`UNSIGNED BIGINT`	Unsigned `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`, `YEAR`, `BIT`
`std::string`, `string_view`, `std::string_view`, `const char*`	`VARCHAR`	`CHAR`, `VARCHAR`, `TEXT` (all sizes), `ENUM`, `SET`, `JSON`, `DECIMAL`, `NUMERIC`
`blob`, `blob_view`	`BLOB`	`BINARY`, `VARBINARY`, `BLOB` (all sizes), `GEOMETRY`
`float`	`FLOAT`	`FLOAT`
`double`	`DOUBLE`	`DOUBLE`
`date`	`DATE`	`DATE`
`datetime`	`DATETIME`	`DATETIME`, `TIMESTAMP`
`time`	`TIME`	`TIME`
`std::nullptr_t`	`NULL`	Any of the other types. Used to insert `NULL`s, for example.
`field_view`	Depends on the actual type stored by the field
`field`	Depends on the actual type stored by the field