MySQL 5.6 C API Developer Guide
This section describes C API data structures other than those used for prepared statements. For information about the latter, see Chapter 9, C API Prepared Statement Data Structures.
This structure represents the handler for one database
connection. It is used for almost all MySQL functions. Do not
try to make a copy of a MYSQL
structure.
There is no guarantee that such a copy will be usable.
This structure represents the result of a query that returns
rows (SELECT
,
SHOW
,
DESCRIBE
,
EXPLAIN
). The information
returned from a query is called the result
set in the remainder of this section.
This is a type-safe representation of one row of data. It is
currently implemented as an array of counted byte strings.
(You cannot treat these as null-terminated strings if field
values may contain binary data, because such values may
contain null bytes internally.) Rows are obtained by calling
mysql_fetch_row()
.
This structure contains metadata: information about a field,
such as the field's name, type, and size. Its members are
described in more detail later in this section. You may obtain
the MYSQL_FIELD
structures for each field
by calling mysql_fetch_field()
repeatedly. Field values are not part of this structure; they
are contained in a MYSQL_ROW
structure.
This is a type-safe representation of an offset into a MySQL
field list. (Used by
mysql_field_seek()
.) Offsets
are field numbers within a row, beginning at zero.
The type used for the number of rows and for
mysql_affected_rows()
,
mysql_num_rows()
, and
mysql_insert_id()
. This type
provides a range of 0
to
1.84e19
.
Some functions that return a row count using this type return
-1 as an unsigned value to indicate an error or exceptional
condition. You can check for -1 by comparing the return value
to (my_ulonglong)-1
(or to
(my_ulonglong)~0
, which is equivalent).
On some systems, attempting to print a value of type
my_ulonglong
does not work. To print such a
value, convert it to unsigned long
and use
a %lu
print format. Example:
printf ("Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));
A boolean type, for values that are true (nonzero) or false (zero).
The MYSQL_FIELD
structure contains the members
described in the following list. The definitions apply primarily
for columns of result sets such as those produced by
SELECT
statements.
MYSQL_FIELD
structures are also used to provide
metadata for OUT
and INOUT
parameters returned from stored procedures executed using prepared
CALL
statements. For such
parameters, some of the structure members have a meaning different
from the meaning for column values.
To interactively view the MYSQL_FIELD
member
values for result sets, invoke the mysql
command with the
--column-type-info
option and
execute some sample queries.
char * name
The name of the field, as a null-terminated string. If the
field was given an alias with an AS
clause,
the value of name
is the alias. For a
procedure parameter, the parameter name.
char * org_name
The name of the field, as a null-terminated string. Aliases are ignored. For expressions, the value is an empty string. For a procedure parameter, the parameter name.
char * table
The name of the table containing this field, if it is not a
calculated field. For calculated fields, the
table
value is an empty string. If the
column is selected from a view, table
names
the view. If the table or view was given an alias with an
AS
clause, the value of
table
is the alias. For a
UNION
, the value is the empty
string. For a procedure parameter, the procedure name.
char * org_table
The name of the table, as a null-terminated string. Aliases
are ignored. If the column is selected from a view,
org_table
names the view. For a
UNION
, the value is the empty
string. For a procedure parameter, the procedure name.
char * db
The name of the database that the field comes from, as a
null-terminated string. If the field is a calculated field,
db
is an empty string. For a
UNION
, the value is the empty
string. For a procedure parameter, the name of the database
containing the procedure.
char * catalog
The catalog name. This value is always
"def"
.
char * def
The default value of this field, as a null-terminated string.
This is set only if you use
mysql_list_fields()
.
unsigned long length
The width of the field. This corresponds to the display length, in bytes.
The server determines the length
value
before it generates the result set, so this is the minimum
length required for a data type capable of holding the largest
possible value from the result column, without knowing in
advance the actual values that will be produced by the query
for the result set.
For string columns, the length
value varies
on the connection character set. For example, if the character
set is latin1
, a single-byte character set,
the length
value for a SELECT
'abc'
query is 3. If the character set is
utf8mb4
, a multibyte character set in which
characters take up to 4 bytes, the length
value is 12.
unsigned long max_length
The maximum width of the field for the result set (the length
in bytes of the longest field value for the rows actually in
the result set). If you use
mysql_store_result()
or
mysql_list_fields()
, this
contains the maximum length for the field. If you use
mysql_use_result()
, the value
of this variable is zero.
The value of max_length
is the length of
the string representation of the values in the result set. For
example, if you retrieve a
FLOAT
column and the
“widest” value is -12.345
,
max_length
is 7 (the length of
'-12.345'
).
If you are using prepared statements,
max_length
is not set by default because
for the binary protocol the lengths of the values depend on
the types of the values in the result set. (See
Chapter 9, C API Prepared Statement Data Structures.)
If you want the max_length
values anyway,
enable the STMT_ATTR_UPDATE_MAX_LENGTH
option with
mysql_stmt_attr_set()
and the
lengths will be set when you call
mysql_stmt_store_result()
.
(See Section 11.3, “mysql_stmt_attr_set()”, and
Section 11.28, “mysql_stmt_store_result()”.)
unsigned int name_length
The length of name
.
unsigned int org_name_length
The length of org_name
.
unsigned int table_length
The length of table
.
unsigned int org_table_length
The length of org_table
.
unsigned int db_length
The length of db
.
unsigned int catalog_length
The length of catalog
.
unsigned int def_length
The length of def
.
unsigned int flags
Bit-flags that describe the field. The
flags
value may have zero or more of the
bits set that are shown in the following table.
Flag Value | Flag Description |
---|---|
NOT_NULL_FLAG |
Field cannot be NULL |
PRI_KEY_FLAG |
Field is part of a primary key |
UNIQUE_KEY_FLAG |
Field is part of a unique key |
MULTIPLE_KEY_FLAG |
Field is part of a nonunique key |
UNSIGNED_FLAG |
Field has the UNSIGNED attribute |
ZEROFILL_FLAG |
Field has the ZEROFILL attribute |
BINARY_FLAG |
Field has the BINARY attribute |
AUTO_INCREMENT_FLAG |
Field has the AUTO_INCREMENT attribute |
ENUM_FLAG |
Field is an ENUM |
SET_FLAG |
Field is a SET |
BLOB_FLAG |
Field is a BLOB or
TEXT (deprecated) |
TIMESTAMP_FLAG |
Field is a TIMESTAMP (deprecated) |
NUM_FLAG |
Field is numeric; see additional notes following table |
NO_DEFAULT_VALUE_FLAG |
Field has no default value; see additional notes following table |
Some of these flags indicate data type information and are
superseded by or used in conjunction with the
MYSQL_TYPE_
value in the xxx
field->type
member
described later:
To check for BLOB
or
TIMESTAMP
values, check
whether type
is
MYSQL_TYPE_BLOB
or
MYSQL_TYPE_TIMESTAMP
. (The
BLOB_FLAG
and
TIMESTAMP_FLAG
flags are unneeded.)
ENUM
and
SET
values are returned as
strings. For these, check that the type
value is MYSQL_TYPE_STRING
and that the
ENUM_FLAG
or
SET_FLAG
flag is set in the
flags
value.
NUM_FLAG
indicates that a column is
numeric. This includes columns with a type of
MYSQL_TYPE_DECIMAL
,
MYSQL_TYPE_NEWDECIMAL
,
MYSQL_TYPE_TINY
,
MYSQL_TYPE_SHORT
,
MYSQL_TYPE_LONG
,
MYSQL_TYPE_FLOAT
,
MYSQL_TYPE_DOUBLE
,
MYSQL_TYPE_NULL
,
MYSQL_TYPE_LONGLONG
,
MYSQL_TYPE_INT24
, and
MYSQL_TYPE_YEAR
.
NO_DEFAULT_VALUE_FLAG
indicates that a
column has no DEFAULT
clause in its
definition. This does not apply to NULL
columns (because such columns have a default of
NULL
), or to
AUTO_INCREMENT
columns (which have an
implied default value).
The following example illustrates a typical use of the
flags
value:
if (field->flags & NOT_NULL_FLAG) printf("Field cannot be null\n");
You may use the convenience macros shown in the following
table to determine the boolean status of the
flags
value.
unsigned int decimals
The number of decimals for numeric fields, and the fractional seconds precision for temporal fields.
unsigned int charsetnr
An ID number that indicates the character set/collation pair for the field.
Normally, character values in result sets are converted to the
character set indicated by the
character_set_results
system
variable. In this case, charsetnr
corresponds to the character set indicated by that variable.
Character set conversion can be suppressed by setting
character_set_results
to
NULL
. In this case,
charsetnr
corresponds to the character set
of the original table column or expression. See also
Connection Character Sets and Collations.
To distinguish between binary and nonbinary data for string
data types, check whether the charsetnr
value is 63. If so, the character set is
binary
, which indicates binary rather than
nonbinary data. This enables you to distinguish
BINARY
from
CHAR
,
VARBINARY
from
VARCHAR
, and the
BLOB
types from the
TEXT
types.
charsetnr
values are the same as those
displayed in the Id
column of the
SHOW COLLATION
statement or the
ID
column of the
INFORMATION_SCHEMA
COLLATIONS
table. You can use
those information sources to see which character set and
collation specific charsetnr
values
indicate:
mysql>SHOW COLLATION WHERE Id = 63;
+-----------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-----------+---------+----+---------+----------+---------+ | binary | binary | 63 | Yes | Yes | 1 | +-----------+---------+----+---------+----------+---------+ mysql>SELECT COLLATION_NAME, CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.COLLATIONS WHERE ID = 33;
+-----------------+--------------------+ | COLLATION_NAME | CHARACTER_SET_NAME | +-----------------+--------------------+ | utf8_general_ci | utf8 | +-----------------+--------------------+
enum enum_field_types type
The type of the field. The type
value may
be one of the MYSQL_TYPE_
symbols shown in
the following table.
Type Value | Type Description |
---|---|
MYSQL_TYPE_TINY |
TINYINT field |
MYSQL_TYPE_SHORT |
SMALLINT field |
MYSQL_TYPE_LONG |
INTEGER field |
MYSQL_TYPE_INT24 |
MEDIUMINT field |
MYSQL_TYPE_LONGLONG |
BIGINT field |
MYSQL_TYPE_DECIMAL |
DECIMAL or
NUMERIC field |
MYSQL_TYPE_NEWDECIMAL |
Precision math DECIMAL or
NUMERIC |
MYSQL_TYPE_FLOAT |
FLOAT field |
MYSQL_TYPE_DOUBLE |
DOUBLE or
REAL field |
MYSQL_TYPE_BIT |
BIT field |
MYSQL_TYPE_TIMESTAMP |
TIMESTAMP field |
MYSQL_TYPE_DATE |
DATE field |
MYSQL_TYPE_TIME |
TIME field |
MYSQL_TYPE_DATETIME |
DATETIME field |
MYSQL_TYPE_YEAR |
YEAR field |
MYSQL_TYPE_STRING |
CHAR or
BINARY field |
MYSQL_TYPE_VAR_STRING |
VARCHAR or
VARBINARY field |
MYSQL_TYPE_BLOB |
BLOB or
TEXT field (use
max_length to determine the maximum
length) |
MYSQL_TYPE_SET |
SET field |
MYSQL_TYPE_ENUM |
ENUM field |
MYSQL_TYPE_GEOMETRY |
Spatial field |
MYSQL_TYPE_NULL |
NULL -type field |
The MYSQL_TYPE_TIME2
,
MYSQL_TYPE_DATETIME2
, and
MYSQL_TYPE_TIMESTAMP2
) type codes are used
only on the server side. Clients see the
MYSQL_TYPE_TIME
,
MYSQL_TYPE_DATETIME
, and
MYSQL_TYPE_TIMESTAMP
codes.
You can use the IS_NUM()
macro to test
whether a field has a numeric type. Pass the
type
value to IS_NUM()
and it evaluates to TRUE if the field is numeric:
if (IS_NUM(field->type)) printf("Field is numeric\n");
ENUM
and
SET
values are returned as
strings. For these, check that the type
value is MYSQL_TYPE_STRING
and that the
ENUM_FLAG
or SET_FLAG
flag is set in the flags
value.