6.2.5 Использование типов столбцов из других баз данных

Чтобы облегчить использование SQL-кода, написанного для баз данных других поставщиков, в MySQL установлено соответствие типов столбцов, как показано в следующей таблице. Это соответствие упрощает применение описаний таблиц баз данных других поставщиков в MySQL:

Соотнесение типов столбцов происходит во время создания таблицы. При создании таблицы с типами столбцов, которые используются другими поставщиками, после запуска команды DESCRIBE имя_таблицы выдается структура данной таблицы с применением принятых в MySQL эквивалентных типов.

User Comments

BLOBs and their subtypes are covered in section
7.3.1.3, on storage requirements for string types.

The common method is to use ENUM('y','n') as the way to store booleans, so far as I've found.

use char(0) with NULL for bools. The column will
then be "" or NULL.

Ross Mellgren wrote: The common method is to use
ENUM('y','n') as the way to store booleans, so far
as I've found.

I write:
That seems to be a MySQL programmer's
idiosyncracy. If you want your tables to work
with external code that's not necessarily designed
for the MySQL world, it looks like you should use
some kind of integer type to represent booleans.
This has tripped me up.

Regarding a tutorial, you may find this useful :
http://devshed.com/Server_Side/MySQL/Grant_Tables/

As far as I can tell, the standard way to
represent booleans in
a SQL database is with some kind of integer. I
know of at least one tool which is designed to
work with half a dozen other databases which
assumes this (Enhydra's DODS--a Java database
access code generator). If I'm right, then it
is a Good Thing to design your tables this way,
so that code that assumes that we do things in the
standard way will work.

Elsewhere in the documentation it states that a
char(0) will create a one bit field, perfect for
a boolean.

For a boolean, CHAR(0) does make sense as far as
you explained it. However,
I simply used the ENUM column type to
indicate "YES" or "NO". Chalk it down to
inexperience, I guess.

More info on using multiple columns in indexes
would be very useful here. Especially what
happens when using multiple columns with a UNIQUE
index!

To change an enum column (if you want to add a
value) the syntax is as follows:

ALTER tableName MODIFY columnName enum
("value1","value2","value3"...);

Remember to include your old values! Is there a
way of adding a new value on its own?

The manual entry here does not explain how to
update a set without keeping the existing
entries. A proper definition is needed.

ALTER TABLE where you change an ENUM definition
appears to correctly handle value changes as well
as values inserted, without adverse affect on
existing data.

However, I have not tested combinatorial changes,
such as inserting a new value plus changing the
label of a later value. I wouldn't expect the
database to be very smart about that.

So a lock request with a WRITE clause gets thrown into some kind of queue if the table in question is already WRITE locked. Does this mean that all subsequent SQL statements for the thread with the queue'd WRITE lock request are thrown into the queue as well? Or do they just get dumped by the wayside?

I have noticed an irregularity with DECIMAL data types:


<DL>
<DD>DECIMAL(1,0) is 2 bytes, not 1 (the negative sign uses one byte, in the positive this extra byte is used by another digit)


<DD>DECIMAL(5,4) is 8 bytes, not 7 (MySQL assumes that there are always 2 digits before a decimal)


<DD>DECIMAL(5,7) is 11 bytes, although technically this should not be allowed (MySQL automatically turns the 5 into a 7, but as always, assumes there are 2 digits before a decimal, so instead of this being .0000000, it is actually 00.0000000)
</DL>
Hope this helps clear some confusion reguarding this data type...

For some reason, I think programmers should make the difference between "Double" and "Float" clear in MySql. In my project on hand, I took it for granted that "Double" and "Float" are basically the same from my C++ knowledge. But the sql statement:
Select ... from ... Where price(double)=64.00 doesn't work while,
Select ... from ... Where price(float)=64.00
works.

I discovered the case-insensitivity of varchar
fields when I used it to store userid. People
could login with mixed case. Joins done with a
case-insensitive field work even with mixed case.

Other column types that are supported in MS SQL
Server that must be changed to work for MySQL
include:
money->real (or double)
varchar[n]->text (or blob) where n > 255

Note following:

mysql> create table test (id integer not null
primary key auto_increment, value set('one','two')
not null default 'one,two');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test (value) values
('one,two');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test (value) values ('one,
two');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+---------+
| id | value |
+----+---------+
| 1 | one,two |
| 2 | one |
+----+---------+

2 rows in set (0.00 sec)

TEXT it is NOT a case insensitive BLOB.
TEXT uses the caracter set, BLOB does not.

Question: Why use Enum('y','n') instead of char
(0) with NULL for bools?

Answer: MySQL is used mostly as a back end for
web applications. This usually requires some kind
of connection with a scripting engine such as
ASP, PHP, Perl, ColdFusion. Some of these engines
can not differentiate between NULL and an empty
string "". ColdFusion I know can not. Therefore
it makes sense to use Enum('y','n') and sacrifice
a few extra bits.

for booleans it is also possible to use tinyint(1) not
null unsigned. 0 = false and everything else
(preferably 1) is
true. In PHP, if booleans are used in if conditions 0
means false and a value for an integer means true

Add your own comment.