This section describes C API data types other than those used for prepared statements. For information about the latter, see Section 21.9.5, “C API Prepared Statement Data types”.
This structure represents a handle to one database connection.
It is used for almost all MySQL functions. You should 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 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
.
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. As of MySQL
5.5.3, MYSQL_FIELD
structures are also used to
provide metadata for OUT
and
INOUT
parameters returned from stored
procedures executed via prepared
CALL
statements. For such
parameters, some of the structure members have a meaning different
from the meaning for column values.
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 a procedure parameter, the parameter name.
char * table
The name of the table containing this field, if it isn't 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 underlying table. 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.
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
Section 21.9.5, “C API Prepared Statement Data types”.) 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 21.9.7.3, “mysql_stmt_attr_set()
”, and
Section 21.9.7.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
following bits set.
Flag Value | Flag Description |
NOT_NULL_FLAG |
Field can't 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 |
NUM_FLAG |
Field is numeric |
ENUM_FLAG |
Field is an ENUM (deprecated) |
SET_FLAG |
Field is a SET (deprecated) |
BLOB_FLAG |
Field is a BLOB or
TEXT (deprecated) |
TIMESTAMP_FLAG |
Field is a TIMESTAMP (deprecated) |
NO_DEFAULT_VALUE_FLAG |
Field has no default value; see additional notes following table |
Use of the BLOB_FLAG
,
ENUM_FLAG
, SET_FLAG
, and
TIMESTAMP_FLAG
flags is deprecated because
they indicate the type of a field rather than an attribute of
its type. It is preferable to test
field->type
against
MYSQL_TYPE_BLOB
,
MYSQL_TYPE_ENUM
,
MYSQL_TYPE_SET
, or
MYSQL_TYPE_TIMESTAMP
instead.
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 can't be null\n");
You may use the following convenience macros to determine the
boolean status of the flags
value.
unsigned int decimals
The number of decimals for numeric fields.
unsigned int charsetnr
An ID number that indicates the character set/collation pair for the field.
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 |
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");
User Comments
From above:
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));
This is naive in that this will ONLY work if the underlying unsigned long long is only as big as an unsigned long! This is only half of the total range of the ull.
A better way is to break it into 2 unsigned longs by shifting the upper 32-bits into a different unsigned long and then printf() both the upper and lower parts as unsigned longs each.
It would seem that MYSQL_FIELD::charsetnr is not a mere reflection of the column definition but also that of the connection to the server. This means that if you are trying to read a column defined as utf8 and your connection is latin1, then charsetnr will be latin1, and you'll lose any characters that don't collate from Unicode to Latin-1.
In general, charsetnr will be character set of the data found in the MYSQL_ROW structure.
MYSQL_TYPE_SET and MYSQL_TYPE_ENUM seem not to be returned for those types in some versions of MySQL 5.0. Instead MYSQL_TYPE_STRING is returned and the flags value is set to SET_FLAG or ENUM_FLAG. The statement, "Use of the BLOB_FLAG, ENUM_FLAG, SET_FLAG, and TIMESTAMP_FLAG flags is deprecated because they indicate the type of a field rather than an attribute of its type" appears to be false. Because of this, MySQL-Python does not handle Set and Enum properly. I hope this helps someone with the problem I had.
Add your own comment.