C API Data Types

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).

Note that 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 offsets.

• my_ulonglong

  A long integer type, used for return values of functions such as mysql_affected_rows(), mysql_num_rows(), and mysql_insert_id(), that return row counts or other potentially large numbers.

  To print a my_ulonglong value, cast it to unsigned long and use a format of '%lu'. The value will not print correctly on some systems if you don't do this.

• my_bool

  A boolean type, used for the return value of mysql_change_user() and mysql_eof().

• MYSQL_FIELD_OFFSET

  This data type is used by the mysql_field_tell() and mysql_field_seek() functions to represent offsets within the set of MYSQL_FIELD structures for the current result set.

Non-Scalar Data Types

MySQL's non-scalar types represent structures or arrays. Every MYSQL and 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 them openly 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. You initialize a MYSQL structure and then pass it to a connection routine to open a server session. After you've established the connection, you can use the handler to issue queries, generate result sets, get error information, and so forth.

• 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 and lengths, and so forth).

• MYSQL_ROW

  The MYSQL_ROW type contains the values for one row of data, represented as an array of counted byte 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], where i ranges from 0 to mysql_num_fields(res_set)-1. (res_set is a pointer to a MYSQL_RES result set.)

  Note that the MYSQL_ROW type is already a pointer, so you declare a row variable like this:

  MYSQL_ROW row; /* correct */

  You don't declare it like this:

  MYSQL_ROW *row; /* incorrect */

  Values in a MYSQL_ROW type have terminating nulls, so non-binary values may 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 the lengths of the values in the row, call mysql_fetch_lengths() like this:

  unsigned long *length; length = mysql_fetch_lengths (res_set);

  The length returned by mysql_fetch_lengths() for NULL values is 0.

• 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 is given by 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 *table;
      char *def;
      enum enum_field_types type;
      unsigned int length;
      unsigned int max_length;
      unsigned int flags;
      unsigned int decimals;
  } MYSQL_FIELD;
  

  MYSQL_FIELD structure members have the following meanings:

• name

  The column name, as a null-terminated string. If the column's values are calculated as the result of an expression, name is that expression in string form. If a column or expression is given a column alias, name is the alias name. For example, the following query results in name values of "my_col", "4*(my_col+1)", and "mc":

  SELECT my_col, 4*(my_col+1), my_col AS mc …

• table

  The name of the table that the column comes from, as a null-terminated string. If the column's values are 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 (my_col) is my_tbl, whereas the table name for the second column (my_col+0) is the empty string:

  SELECT my_col, my_col+0 FROM my_tbl …

• def

  The default value for the column, as a null-terminated string. This member of the MYSQL_FIELD structure is set only if you call mysql_list_fields(), and is NULL otherwise. (Column default values can also be obtained by issuing a DESCRIBE tbl_name or SHOW FIELDS FROM tbl_name query and examining the result set.)

• type

  The column type. The type for a column calculated as the result of an expression is determined from the types of the elements in the expression. For example, if my_col is a VARCHAR(20) column, type is FIELD_TYPE_VAR_STRING, whereas type for LENGTH(my_col) is FIELD_TYPE_LONGLONG.

  The possible type values are listed in mysql_com.h and shown in Table F.1.

  Table F.1. MYSQL_FIELD typeMember Values

  typeValue	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.

• length

  The length of the column, as specified in the CREATE TABLE statement used to create the table. The length for a column calculated as the result of an expression 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 column in a result set contains the values "Bill," "Jack," and "Belvidere," max_length for the column is 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. Attributes are represented by individual bits within the flags value. The bits may be tested via the bitmask constants shown in Table F.2. For example, to determine whether or not a column's values are UNSIGNED, test the flags value like this:

  if (field->flags &UNSIGNED_FLAG)
      printf ("%s values are UNSIGNED
  ", field->name);
  

  Table F.2. MYSQL_FIELD flagsMember Values

  flagsValue	Meaning
  AUTO_INCREMENT_FLAG	Column has theAUTO_INCREMENTattribute
  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 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

  
  

  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.3 lists these deprecated constants.

  Table F.3. Deprecated MYSQL_FIELD flags Member Values

  flags Value	Meaning
  BLOB_FLAG	Column contains BLOB 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; decimals is zero for non-numeric columns. For example, a DECIMAL(8,3) column has a decimals value of 3, whereas a BLOB column has a value of 0.

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
", 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
", 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
", field->name);

IS_BLOB() is true if the column is a BLOB or TEXT. However, this macro tests the deprecated flags member BLOB_FLAGbit, so IS_BLOB() is deprecated as well.