Oracle 5.0 Reference Manual Download

Page: 872 / 3232

String Types

852

• For indexes on

BLOB

and

TEXT

columns, you must specify an index prefix length. For

CHAR

and

VARCHAR

, a prefix length is optional. See

Section 8.5.1, “Column Indexes”

•

BLOB

and

TEXT

columns cannot have

DEFAULT

values.

If you use the

BINARY

attribute with a

TEXT

data type, the column is assigned the binary collation of

the column character set.

LONG

and

LONG VARCHAR

map to the

MEDIUMTEXT

data type. This is a compatibility feature.

MySQL Connector/ODBC defines

BLOB

values as

LONGVARBINARY

and

TEXT

values as

LONGVARCHAR

Because

BLOB

and

TEXT

values can be extremely long, you might encounter some constraints in using

them:

• Only the first

max_sort_length

[471]

bytes of the column are used when sorting. The default value

max_sort_length

[471]

is 1024. You can make more bytes significant in sorting or grouping by

increasing the value of

max_sort_length

[471]

at server startup or runtime. Any client can change

the value of its session

max_sort_length

[471]

variable:

mysql>

SET max_sort_length = 2000;

mysql>

SELECT id, comment FROM t

ORDER BY comment;

• Instances of

BLOB

TEXT

columns in the result of a query that is processed using a temporary

table causes the server to use a table on disk rather than in memory because the

MEMORY

storage

engine does not support those data types (see

Section 8.8.5, “How MySQL Uses Internal Temporary

Tables”

). Use of disk incurs a performance penalty, so include

BLOB

TEXT

columns in the query

result only if they are really needed. For example, avoid using

SELECT *

, which selects all columns.

• The maximum size of a

BLOB

TEXT

object is determined by its type, but the largest value you

actually can transmit between the client and server is determined by the amount of available memory
and the size of the communications buffers. You can change the message buffer size by changing
the value of the

max_allowed_packet

[466]

variable, but you must do so for both the server and

your client program. For example, both

mysql

and

mysqldump

enable you to change the client-side

max_allowed_packet

[466]

value. See

Section 8.9.2, “Tuning Server Parameters”

Section 4.5.1,

“

mysql

— The MySQL Command-Line Tool”

, and

Section 4.5.4, “

mysqldump

— A Database

Backup Program”

. You may also want to compare the packet sizes and the size of the data objects

you are storing with the storage requirements, see

Section 11.2, “Data Type Storage Requirements”

Each

BLOB

TEXT

value is represented internally by a separately allocated object. This is in contrast

to all other data types, for which storage is allocated once per column when the table is opened.

In some cases, it may be desirable to store binary data such as media files in

BLOB

TEXT

columns.

You may find MySQL's string handling functions useful for working with such data. See

Section 12.5,

“String Functions”

. For security and other reasons, it is usually preferable to do so using application

code rather than giving application users the

FILE

[577]

privilege. You can discuss specifics for various

languages and platforms in the MySQL Forums (

http://forums.mysql.com/

11.1.6.4. The

ENUM

Type

ENUM

is a string object with a value chosen from a list of permitted values that are enumerated

explicitly in the column specification at table creation time.

An enumeration value must be a quoted string literal; it may not be an expression, even one that
evaluates to a string value. For example, you can create a table with an

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands