| Data types for the MySQL client library are designed to represent the entities you deal with in the course of a session with the server. There are types for the connection itself, for results from a query, for a row within a result, and for metadata (descriptive information about the columns making up a result). The terms "column" and "field" are synonymous in the following discussion. Scalar Data Types MySQL's scalar data types represent values such as very large integers, boolean values, and field or row offsets. my_bool A boolean type, used for the return value of mysql_change_user(), mysql_eof(), and mysql_thread_init(). my_ulonglong A long integer type, used for the return value of functions that return row counts or other potentially large numbers, such as mysql_affected_rows()mysql_num_rows(), and mysql_insert_id()., To print a my_ulonglong value, cast it to unsigned long and use a format of %lu. For example: printf ("Row count = %lu\n", (unsigned long) mysql_affected_rows (conn)); The value will not print correctly on some systems if you don't do this, because there is no standard for printing long long values with printf(). However, if the value to be printed might actually exceed the maximum allowed by unsigned long (232 1), %lu won't work, either. You'll need to check your printf() documentation to see if there is some implementation-specific means of printing the value. For example, a %llu format specifier might be available. MYSQL_FIELD_OFFSET This data type is used by the mysql_field_seek() and mysql_field_tell() functions to represent offsets within the set of MYSQL_FIELD structures for the current result set. MYSQL_ROW_OFFSET This data type is used by the mysql_row_seek() and mysql_row_tell() functions to represent offsets within the set of rows for the current result set. Non-Scalar Data Types MySQL's non-scalar types represent structures or arrays. Any instance of a MYSQL or MYSQL_RES structure should be considered as a "black box" that is, you should refer only to the structure itself, not to elements within the structure. The MYSQL_ROW and MYSQL_FIELD types have no such restriction. You access elements of these structures freely to obtain data and metadata returned as a result of a query. MYSQL The primary client library type is the MYSQL structure, which is used for connection handlers. A handler contains information about the state of a connection with a server. To open a session with the server, initialize a MYSQL structure with mysql_init() and then pass it to mysql_real_connect(). After you've established the connection, use the handler to issue queries, generate result sets, get error information, and so on. When you're done with the connection, pass the handler to mysql_close(), after which the handler should no longer be used. MYSQL_FIELD The client library uses MYSQL_FIELD structures to represent metadata about the columns in the result set, one structure per column. The number of MYSQL_FIELD structures in the set can be determined by calling mysql_num_fields(). You can access successive field structures by calling mysql_fetch_field() or move back and forth among structures with mysql_field_tell() and mysql_field_seek(). The MYSQL_FIELD structure is useful for presenting or interpreting the contents of data rows. It looks like this: typedef struct st_mysql_field { char *name; char *org_name; char *table; char *org_table; char *db; char *def; unsigned long length; unsigned long max_length; unsigned int flags; unsigned int decimals; enum enum_field_types type; } MYSQL_FIELD; MYSQL_FIELD structure members have the following meanings: name The column name, as a null-terminated string. For a column that is calculated as the result of an expression, name is that expression in string form. If a column or expression is given an alias, name is the alias name. For example, the following query results in name values of "mycol", "4*(mycol+1)", "mc", and "myexpr": SELECT mycol, 4*(mycol+1), mycol AS mc, 4*(mycol+1) AS myexpr ... org_name This member is like name, except that column aliases are ignored. That is, org_name represents the original column name. For a column that is calculated as the result of an expression, org_name is an empty string. This member is unused prior to MySQL 4.1. table The name of the table that the column comes from, as a null-terminated string. If the table was given an alias, table is the alias name. For a column that is calculated as the result of an expression, table is an empty string. For example, if you issue a query like the following, the table name for the first column is mytbl, whereas the table name for the second column is the empty string: SELECT mycol, mycol+0 FROM mytbl ... org_table This member is like table, except that table aliases are ignored; that is, org_table represents the original table name. For a column that is calculated as the result of an expression, org_table is an empty string. This member is unused prior to MySQL 4.1. db The database in which the table containing the column is located as a null-terminated string. For a column that is calculated as the result of an expression, db is an empty string. This member is unused prior to MySQL 4.1. def The default value for the column, as a null-terminated string. This member of the MYSQL_FIELD structure is set only for result sets obtained by calling mysql_list_fields() and is NULL otherwise. Default values for table columns also can be obtained by issuing a DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name query and examining the result set. length The length of the column, as specified in the CREATE TABLE statement used to create the table. For a column that is calculated as the result of an expression, the length is determined from the elements in the expression. max_length The length of the longest column value actually present in the result set. For example, if a string column in a result set contains the values "Bill", "Jack", and "Belvidere", the value of max_length for the column will be 9. Because the max_length value can be determined only after all the rows have been seen, it is meaningful only for result sets created with mysql_store_result(). max_length is 0 for result sets created with mysql_use_result(). flags The flags member specifies attributes for the columns. Within the flags value, attributes are represented by individual bits, which can be tested via the bitmask constants shown in Table F.1. For example, to determine whether or not a column's values are UNSIGNED, test the flags value as follows: if (field->flags & UNSIGNED_FLAG) printf ("%s values are UNSIGNED\n", field->name); Table F.1. MYSQL_FIELD flags Member Values | flags Value | Meaning | | AUTO_INCREMENT_FLAG | Column has the AUTO_INCREMENT attribute | | BINARY_FLAG | Column has the BINARY attribute | | MULTIPLE_KEY_FLAG | Column is a part of a non-unique index | | NOT_NULL_FLAG | Column cannot contain NULL values | | PRI_KEY_FLAG | Column is a part of a PRIMARY KEY | | UNIQUE_KEY_FLAG | Column is a part of a UNIQUE index | | UNSIGNED_FLAG | Column has the UNSIGNED attribute | | ZEROFILL_FLAG | Column has the ZEROFILL attribute | BINARY_FLAG is set for case-sensitive string columns. This includes columns for which the BINARY keyword is specified explicitly (such as CHAR BINARY), as well as BLOB columns. A few flags constants indicate column types rather than column attributes; they are now deprecated because you should use field->type to determine the column type. Table F.2 lists these deprecated constants. Table F.2. Deprecated MYSQL_FIELD flags Member Values | flags Value | Meaning | | BLOB_FLAG | Column contains BLOB or TEXT values | | ENUM_FLAG | Column contains ENUM values | | SET_FLAG | Column contains SET values | | TIMESTAMP_FLAG | Column contains TIMESTAMP values | decimals The number of decimals for numeric columns, zero for non-numeric columns. For example, the decimals value is 3 for a DECIMAL(8,3) column but 0 for a BLOB column. type The column type. For a column that is calculated as the result of an expression, type is determined from the types of the elements in the expression. For example, if mycol is a VARCHAR(20) column, type is FIELD_TYPE_VAR_STRING whereas type for LENGTH(mycol) is FIELD_TYPE_LONGLONG., The possible type values are listed in mysql_com.h and shown in Table F.3. Table F.3. MYSQL_FIELD type Member Values | type Value | Column Type | | FIELD_TYPE_BLOB | BLOB or TEXT | | FIELD_TYPE_DATE | DATE | | FIELD_TYPE_DATETIME | DATETIME | | FIELD_TYPE_DECIMAL | DECIMAL or NUMERIC | | FIELD_TYPE_DOUBLE | DOUBLE or REAL | | FIELD_TYPE_ENUM | ENUM | | FIELD_TYPE_FLOAT | FLOAT | | FIELD_TYPE_INT24 | MEDIUMINT | | FIELD_TYPE_LONG | INT | | FIELD_TYPE_LONGLONG | BIGINT | | FIELD_TYPE_NULL | NULL | | FIELD_TYPE_SET | SET | | FIELD_TYPE_SHORT | SMALLINT | | FIELD_TYPE_STRING | CHAR | | FIELD_TYPE_TIME | TIME | | FIELD_TYPE_TIMESTAMP | TIMESTAMP | | FIELD_TYPE_TINY | TINYINT | | FIELD_TYPE_VAR_STRING | VARCHAR | | FIELD_TYPE_YEAR | YEAR | You might see references to FIELD_TYPE_CHAR in older source files; that was a one-byte type that is now called FIELD_TYPE_TINY. Similarly, FIELD_TYPE_INTERVAL is now called FIELD_TYPE_ENUM. MYSQL_RES Queries such as SELECT or SHOW that return data to the client do so by means of a result set, represented as a MYSQL_RES structure. This structure contains information about the rows returned by the query. After you have a result set, you can call API functions to get result set data (the data values in each row of the set) or metadata (information about the result, such as how many columns there are, their types, their lengths, and so on). MYSQL_ROW The MYSQL_ROW type contains the values for one row of data, represented as an array of strings. All values are returned in string form (even numbers), except that if a value in a row is NULL it is represented in the MYSQL_ROW structure by a C NULL pointer. The number of values in a row is given by mysql_num_fields()., The i-th column value in a row is given by row[i]. Values of i range from 0 to mysql_num_fields(res_set) 1, where res_set is a pointer to a MYSQL_RES result set. Note that the MYSQL_ROW type is already a pointer, so you should declare a row variable as follows: MYSQL_ROW row; /* correct */ Not like the following: MYSQL_ROW *row; /* incorrect */ Values in a MYSQL_ROW array have terminating nulls, so non-binary values can be treated as null-terminated strings. However, data values that may contain binary data might contain null bytes internally and should be treated as counted strings. To get a pointer to an array that contains the lengths of the values in the row, call mysql_fetch_lengths() as follows: unsigned long *length; length = mysql_fetch_lengths (res_set); The length of the i-th column value in a row is given by length[i]. If the column value is NULL, the length will be zero. Accessor Macros mysql.h contains a few macros that allow you to test MYSQL_FIELD members more conveniently. IS_NUM() tests the type member; the others listed here test the flags member. IS_NUM() is true (non-zero) if values in the column have a numeric type: if (IS_NUM (field->type)) printf ("Field %s is numeric\n", field->name); IS_PRI_KEY() is true if the column is part of a PRIMARY KEY: if (IS_PRI_KEY (field->flags)) printf ("Field %s is part of primary key\n", field->name); IS_NOT_NULL() is true if the column cannot contain NULL values: if (IS_NOT_NULL (field->flags)) printf ("Field %s values cannot be NULL\n", field->name); IS_BLOB() is true if the column is a BLOB or TEXT. However, this macro tests the deprecated BLOB_FLAG bit of the flags member, so IS_BLOB() is deprecated as well. |
