1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
# Considering MySQL? Choose Something Else
Thesis: databases fill roles ranging between pure storage and extensive data
processing; MySQL is differently bad at both poles.
(Real apps fall between these poles, and suffer variably from either set of
MySQL flaws.)
## Storage
Storage systems have four properties:
1. Take and store data they receive from applications.
2. Keep that data safe against loss or accidental change.
3. Provide stored data to applications on demand.
4. Give administrators effective management tools.
In a truly "pure" storage application, data-comprehension features
(constraints and relationships, nontrivial functions and aggregates) would go
totally unused. There is a time and a place for this: the return of "NoSQL"
storage systems attests to that.
Pure storage systems tend to be closely coupled to their "main" application:
consider most web/server app databases. "Secondary" clients tend to be
read-only (reporting applications, monitoring) or to be utilities in service
of the main application (migration tools, documentation tools). If you believe
constraints, validity checks, and other comprehension features can be
implemented in "the application", you are probably thinking of databases close
to this pole.
### Storing Data
MySQL has many edge cases which reduce the predictability of its behaviour
when storing information. Most of these edge cases are documented, but violate
the principle of least surprise (not to mention the expectations of users
familiar with other SQL implementations).
* Implicit conversions (particularly to and from string types) can modify
MySQL's behaviour.
* Many implicit conversions are also silent (no warning, no diagnostic),
by design, making it more likely developers are entirely unaware of
them until one does something surprising.
* Conversions that violate basic constraints (range, length) of the output
type often coerce data rather than failing.
* Sometimes this raises a warning; does your app check for those?
* This behaviour is unlike many typed systems (but closely like PHP and
remotely like Perl).
* Conversion behaviour depends on a per-connection configuration value with a
large constellation of possible states, making it harder to carry
expectations from manual testing over to code.
### Preserving Data
|
