Buy this Reference Manual in softcover from Barnes & Noble!
Чтобы облегчить использование SQL-кода, написанного для баз данных других поставщиков, в MySQL установлено соответствие типов столбцов, как показано в следующей таблице. Это соответствие упрощает применение описаний таблиц баз данных других поставщиков в MySQL:
| Тип иного поставщика | Тип MySQL |
BINARY(NUM) |
CHAR(NUM) BINARY |
CHAR VARYING(NUM) |
VARCHAR(NUM) |
FLOAT4 |
FLOAT |
FLOAT8 |
DOUBLE |
INT1 |
TINYINT |
INT2 |
SMALLINT |
INT3 |
MEDIUMINT |
INT4 |
INT |
INT8 |
BIGINT |
LONG VARBINARY |
MEDIUMBLOB |
LONG VARCHAR |
MEDIUMTEXT |
MIDDLEINT |
MEDIUMINT |
VARBINARY(NUM) |
VARCHAR(NUM) BINARY |
Соотнесение типов столбцов происходит во время создания таблицы. При создании таблицы с типами столбцов, которые используются другими поставщиками, после запуска команды DESCRIBE имя_таблицы выдается структура данной таблицы с применением принятых в MySQL эквивалентных типов.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
BLOBs and their subtypes are covered in section
7.3.1.3, on storage requirements for string types.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
The common method is to use ENUM('y','n') as the way to store booleans, so far as I've found.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
use char(0) with NULL for bools. The column will
then be "" or NULL.
| Posted by Marshall Abrams on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
Regarding a tutorial, you may find this useful :
http://devshed.com/Server_Side/MySQL/Grant_Tables/
| Posted by Marshall Abrams on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
Elsewhere in the documentation it states that a
char(0) will create a one bit field, perfect for
a boolean.
| Posted by John K. on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
More info on using multiple columns in indexes
would be very useful here. Especially what
happens when using multiple columns with a UNIQUE
index!
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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?
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
The manual entry here does not explain how to
update a set without keeping the existing
entries. A proper definition is needed.
| Posted by Randy Harmon on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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?
| Posted by on Wednesday December 18 2002, @5:28pm | [Delete] [Edit] |
I have noticed an irregularity with DECIMAL data types:
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by on Wednesday December 18 2002, @5:27pm | [Delete] [Edit] |
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
| Posted by [name withheld] on Wednesday December 18 2002, @5:27pm | [Delete] [Edit] |
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)
| Posted by on Wednesday December 18 2002, @5:27pm | [Delete] [Edit] |
TEXT it is NOT a case insensitive BLOB.
TEXT uses the caracter set, BLOB does not.
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by on Tuesday December 3 2002, @4:29pm | [Delete] [Edit] |
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.