Rechercher dans le manuel MySQL
11.4.3 The BLOB and TEXT Types
A BLOB
is a binary large object that can hold
a variable amount of data. The four BLOB
types are TINYBLOB
, BLOB
,
MEDIUMBLOB
, and LONGBLOB
.
These differ only in the maximum length of the values they can
hold. The four TEXT
types are
TINYTEXT
, TEXT
,
MEDIUMTEXT
, and LONGTEXT
.
These correspond to the four BLOB
types and
have the same maximum lengths and storage requirements. See
Section 11.8, “Data Type Storage Requirements”.
BLOB
values are treated as binary strings
(byte strings). They have the binary
character set and collation, and comparison and sorting are
based on the numeric values of the bytes in column values.
TEXT
values are treated as nonbinary strings
(character strings). They have a character set other than
binary
, and values are sorted and compared
based on the collation of the character set.
If strict SQL mode is not enabled and you assign a value to a
BLOB
or TEXT
column that
exceeds the column's maximum length, the value is truncated to
fit and a warning is generated. For truncation of nonspace
characters, you can cause an error to occur (rather than a
warning) and suppress insertion of the value by using strict SQL
mode. See Section 5.1.11, “Server SQL Modes”.
Truncation of excess trailing spaces from values to be inserted
into TEXT
columns always
generates a warning, regardless of the SQL mode.
For TEXT
and BLOB
columns,
there is no padding on insert and no bytes are stripped on
select.
If a TEXT
column is indexed, index entry
comparisons are space-padded at the end. This means that, if the
index requires unique values, duplicate-key errors will occur
for values that differ only in the number of trailing spaces.
For example, if a table contains 'a'
, an
attempt to store 'a '
causes a
duplicate-key error. This is not true for
BLOB
columns.
In most respects, you can regard a BLOB
column as a VARBINARY
column that
can be as large as you like. Similarly, you can regard a
TEXT
column as a
VARCHAR
column.
BLOB
and TEXT
differ from
VARBINARY
and
VARCHAR
in the following ways:
For indexes on
BLOB
andTEXT
columns, you must specify an index prefix length. ForCHAR
andVARCHAR
, a prefix length is optional. See Section 8.3.5, “Column Indexes”.
If you use the BINARY
attribute with a
TEXT
data type, the column is assigned the
binary (_bin
) 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
bytes of the column are used when sorting. The default value ofmax_sort_length
is 1024. You can make more bytes significant in sorting or grouping by increasing the value ofmax_sort_length
at server startup or runtime. Any client can change the value of its sessionmax_sort_length
variable:Instances of
BLOB
orTEXT
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 theMEMORY
storage engine does not support those data types (see Section 8.4.4, “Internal Temporary Table Use in MySQL”). Use of disk incurs a performance penalty, so includeBLOB
orTEXT
columns in the query result only if they are really needed. For example, avoid usingSELECT *
, which selects all columns.The maximum size of a
BLOB
orTEXT
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 themax_allowed_packet
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-sidemax_allowed_packet
value. See Section 5.1.1, “Configuring the Server”, Section 4.5.1, “mysql — The MySQL Command-Line Client”, 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.8, “Data Type Storage Requirements”
Each BLOB
or 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
or
TEXT
columns. You may find MySQL's string
handling functions useful for working with such data. See
Section 12.5, “String Functions and Operators”. For security and other
reasons, it is usually preferable to do so using application
code rather than giving application users the
FILE
privilege. You can discuss
specifics for various languages and platforms in the MySQL
Forums (http://forums.mysql.com/).
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-blob.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.