MySQL - SELECT INTO TABLE
MySQL doesn't yet support the Oracle SQL extension:
SELECT ... INTO TABLE .... MySQL supports instead the
ANSI SQL syntax INSERT INTO ... SELECT ..., which is
basically the same thing.
Alternatively, you can use SELECT INTO OUTFILE... or
CREATE TABLE ... SELECT to solve your problem.
MySQL - Transactions
As MySQL does nowadays support transactions, the
following discussion is only valid if you are only using
the non-transaction-safe table types.
The question is often asked, by the curious and the
critical, ``Why is MySQL not a transactional database?''
or ``Why does MySQL not support transactions?''
MySQL has made a conscious decision to support another
paradigm for data integrity, ``atomic operations.'' It
is our thinking and experience that atomic operations
offer equal or even better integrity with much better
performance. We, nonetheless, appreciate and understand
the transactional database paradigm and plan, within the
next few releases, to introduce transaction-safe tables
on a per table basis. We will be giving our users the
possibility to decide if they need the speed of atomic
operations or if they need to use transactional features
in their applications.
How does one use the features of MySQL to maintain
rigorous integrity and how do these features compare
with the transactional paradigm?
First, in the transactional paradigm, if your
applications are written in a way that is dependent on
the calling of ``rollback'' instead of ``commit'' in
critical situations, then transactions are more
convenient. Moreover, transactions ensure that
unfinished updates or corrupting activities are not
committed to the database; the server is given the
opportunity to do an automatic rollback and your
database is saved.
MySQL, in almost all cases, allows you to solve for
potential problems by including simple checks before
updates and by running simple scripts that check the
databases for inconsistencies and automatically repair
or warn if such occurs. Note that just by using the
MySQL log or even adding one extra log, one can normally
fix tables perfectly with no data integrity loss.
Moreover, fatal transactional updates can be rewritten
to be atomic. In fact,we will go so far as to say that
all integrity problems that transactions solve can be
done with LOCK TABLES or atomic updates, ensuring that
you never will get an automatic abort from the database,
which is a common problem with transactional databases.
Not even transactions can prevent all loss if the server
goes down. In such cases even a transactional system can
lose data. The difference between different systems lies
in just how small the time-lap is where they could lose
data. No system is 100% secure, only ``secure enough.''
Even Oracle, reputed to be the safest of transactional
databases, is reported to sometimes lose data in such
To be safe with MySQL, you only need to have backups and
have the update logging turned on. With this you can
recover from any situation that you could with any
transactional database. It is, of course, always good to
have backups, independent of which database you use.
The transactional paradigm has its benefits and its
drawbacks. Many users and application developers depend
on the ease with which they can code around problems
where an abort appears to be, or is necessary, and they
may have to do a little more work with MySQL to either
think differently or write more. If you are new to the
atomic operations paradigm, or more familiar or more
comfortable with transactions, do not jump to the
conclusion that MySQL has not addressed these issues.
Reliability and integrity are foremost in our minds.
Recent estimates indicate that there are more than
1,000,000 mysqld servers currently running, many of
which are in production environments. We hear very, very
seldom from our users that they have lost any data, and
in almost all of those cases user error is involved.
This is, in our opinion, the best proof of MySQL's
stability and reliability.
Lastly, in situations where integrity is of highest
importance, MySQL's current features allow for
transaction-level or better reliability and integrity.
If you lock tables with LOCK TABLES, all updates will
stall until any integrity checks are made. If you only
obtain a read lock (as opposed to a write lock), then
reads and inserts are still allowed to happen. The new
inserted records will not be seen by any of the clients
that have a READ lock until they release their read
locks. With INSERT DELAYED you can queue inserts into a
local queue, until the locks are released, without
having the client wait for the insert to complete.
``Atomic,'' in the sense that we mean it, is nothing
magical. It only means that you can be sure that while
each specific update is running, no other user can
interfere with it, and there will never be an automatic
rollback (which can happen on transaction based systems
if you are not very careful). MySQL also guarantees that
there will not be any dirty reads. You can find some
example of how to write atomic updates in the
We have thought quite a bit about integrity and
performance, and we believe that our atomic operations
paradigm allows for both high reliability and extremely
high performance, on the order of three to five times
the speed of the fastest and most optimally tuned of
transactional databases. We didn't leave out
transactions because they are hard to do. The main
reason we went with atomic operations as opposed to
transactions is that by doing this we could apply many
speed optimizations that would not otherwise have been
Many of our users who have speed foremost in their minds
are not at all concerned about transactions. For them
transactions are not an issue. For those of our users
who are concerned with or have wondered about
transactions vis-a-vis MySQL, there is a ``MySQL way''
as we have outlined above. For those where safety is
more important than speed, we recommend them to use the
BDB tables for all their critical data.
One final note: We are currently working on a safe
replication schema that we believe to be better than any
commercial replication system we know of. This system
will work most reliably under the atomic operations,
non-transactional, paradigm. Stay tuned.
Page Numbers : 1