1 | ========================
|
---|
2 | MySQL Database Reference
|
---|
3 | ========================
|
---|
4 |
|
---|
5 | MySQL Notes
|
---|
6 | ===========
|
---|
7 |
|
---|
8 | Django expects the database to support transactions, referential integrity,
|
---|
9 | and Unicode (UTF-8). Fortunately MySQL_ has all these features as available as
|
---|
10 | far back as 3.23. While it may be possible to use 3.23 or 4.0, you will probably
|
---|
11 | have less trouble if you use 4.1 or 5.0.
|
---|
12 |
|
---|
13 | MySQL-4.1
|
---|
14 | ---------
|
---|
15 |
|
---|
16 | MySQL-4.1_ has greatly improved support for character sets. It is possible to
|
---|
17 | set different default character sets on the database, table, and column. Previous
|
---|
18 | versions have only a server-wide character set setting. It's also the first version
|
---|
19 | where the character set can be changed on the fly. 4.1 also has support for views,
|
---|
20 | but these are not currently used by Django.
|
---|
21 |
|
---|
22 | MySQL-5.0
|
---|
23 | ---------
|
---|
24 |
|
---|
25 | MySQL-5.0_ adds the ``information_schema`` database, which contains detailed
|
---|
26 | data on all database schema. This is used for Django's ``inspectdb`` feature,
|
---|
27 | when it is available. 5.0 also has support for stored procedures, but these
|
---|
28 | are not currently used by Django.
|
---|
29 |
|
---|
30 | .. _MySQL: http://www.mysql.com/
|
---|
31 | .. _MySQL-4.1: http://dev.mysql.com/doc/refman/4.1/en/index.html
|
---|
32 | .. _MySQL-5.0: http://dev.mysql.com/doc/refman/5.0/en/index.html
|
---|
33 |
|
---|
34 | Storage Engines
|
---|
35 | ---------------
|
---|
36 |
|
---|
37 | MySQL has several `storage engines`_ (previously called table types). You can
|
---|
38 | change the default storage engine in the server configuration.
|
---|
39 |
|
---|
40 | The default one is MyISAM_. The main drawback of MyISAM is that it does not
|
---|
41 | currently have support for transactions or foreign keys. On the plus side, it
|
---|
42 | is currently the only engine that supports full-text indexing and searching.
|
---|
43 |
|
---|
44 | The InnoDB_ engine is fully transactional and supports foreign key references.
|
---|
45 |
|
---|
46 | The BDB_ engine, like InnoDB, is also fully transactional and supports foreign
|
---|
47 | key references. However, it's use seems to be somewhat deprecated.
|
---|
48 |
|
---|
49 | `Other storage engines`_, including SolidDB_ and Falcon_, are on the horizon.
|
---|
50 | For now, InnoDB is probably your best choice.
|
---|
51 |
|
---|
52 | .. _storage engines: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
|
---|
53 | .. _MyISAM: http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html
|
---|
54 | .. _BDB: http://dev.mysql.com/doc/refman/5.0/en/bdb-storage-engine.html
|
---|
55 | .. _InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb.html
|
---|
56 | .. _Other storage engines: http://dev.mysql.com/doc/refman/5.1/en/storage-engines-other.html
|
---|
57 | .. _SolidDB: http://forge.mysql.com/projects/view.php?id=139
|
---|
58 | .. _Falcon: http://dev.mysql.com/doc/falcon/en/index.html
|
---|
59 |
|
---|
60 | MySQLdb Notes
|
---|
61 | =============
|
---|
62 |
|
---|
63 | `MySQLdb`_ is the Python interface to MySQL. 1.2.1 is the first version which
|
---|
64 | has support for MySQL-4.1 and newer. If you are trying to use an older version
|
---|
65 | of MySQL, then 1.2.0 *may* work for you.
|
---|
66 |
|
---|
67 | .. _MySQLdb: http://sourceforge.net/projects/mysql-python
|
---|
68 |
|
---|
69 | Creating your database
|
---|
70 | ----------------------
|
---|
71 |
|
---|
72 | You can `create your database`_ using the command-line tools and this SQL::
|
---|
73 |
|
---|
74 | CREATE DATABASE <dbname> CHARACTER SET utf8;
|
---|
75 |
|
---|
76 | This ensures all tables and columns will use utf8 by default.
|
---|
77 |
|
---|
78 | .. _create your database: http://dev.mysql.com/doc/refman/5.0/en/create-database.html
|
---|
79 |
|
---|
80 | Connecting to the database
|
---|
81 | --------------------------
|
---|
82 |
|
---|
83 | Refer to the `settings documentation`_.
|
---|
84 |
|
---|
85 | Connection settings are used in this order:
|
---|
86 |
|
---|
87 | 1. ``DATABASE_OPTIONS``
|
---|
88 | 2. ``DATABASE_NAME``, ``DATABASE_USER``, ``DATABASE_PASSWORD``, ``DATABASE_HOST``,
|
---|
89 | ``DATABASE_PORT``
|
---|
90 | 3. MySQL option files.
|
---|
91 |
|
---|
92 | In other words, if you set the name of the database in ``DATABASE_OPTIONS``,
|
---|
93 | this will take precedence over ``DATABASE_NAME``, which would override
|
---|
94 | anything in a `MySQL option file`_.
|
---|
95 |
|
---|
96 | Here's a sample configuration which uses a MySQL option file::
|
---|
97 |
|
---|
98 | # settings.py
|
---|
99 | DATABASE_ENGINE = "mysql"
|
---|
100 | DATABASE_OPTIONS = {
|
---|
101 | 'read_default_file': '/path/to/my.cnf',
|
---|
102 | }
|
---|
103 |
|
---|
104 | # my.cnf
|
---|
105 | [client]
|
---|
106 | database = DATABASE_NAME
|
---|
107 | user = DATABASE_USER
|
---|
108 | passwd = DATABASE_PASSWORD
|
---|
109 | default-character-set = utf8
|
---|
110 |
|
---|
111 | There are several other MySQLdb connection options which may be useful, such
|
---|
112 | as ``ssl``, ``use_unicode``, ``init_command``, and ``sql_mode``; consult the
|
---|
113 | `MySQLdb documentation`_ for more details.
|
---|
114 |
|
---|
115 | .. _settings documentation: http://www.djangoproject.com/documentation/settings/#database-engine
|
---|
116 | .. _MySQL option file: http://dev.mysql.com/doc/refman/5.0/en/option-files.html
|
---|
117 | .. _MySQLdb documentation: http://mysql-python.sourceforge.net/
|
---|
118 |
|
---|
119 | Creating your tables
|
---|
120 | --------------------
|
---|
121 |
|
---|
122 | When Django generates the schema, it doesn't specify a storage engine, so they
|
---|
123 | will be created with whatever default `storage engine`__ your database server
|
---|
124 | is configured for. The easiest solution is to set your database server's default
|
---|
125 | storage engine to the desired engine.
|
---|
126 |
|
---|
127 | __ `storage engines`_
|
---|
128 |
|
---|
129 | If you are using a hosting service and can't change your server's default
|
---|
130 | storage engine, you have a couple of options.
|
---|
131 |
|
---|
132 | After the tables is created, all that is needed to convert it to a new storage
|
---|
133 | engine (such as InnoDB) is::
|
---|
134 |
|
---|
135 | ALTER TABLE <tablename> ENGINE=INNODB;
|
---|
136 |
|
---|
137 | With a lot of tables, this can be tedious.
|
---|
138 |
|
---|
139 | Another option is to use the ``init_command`` option for MySQLdb prior to
|
---|
140 | creating your tables::
|
---|
141 |
|
---|
142 | DATABASE_OPTIONS = {
|
---|
143 | ...
|
---|
144 | init_command = "SET storage_engine=INNODB",
|
---|
145 | ...
|
---|
146 | }
|
---|
147 |
|
---|
148 | This sets the default storage engine upon connecting to the database. After
|
---|
149 | your tables are set up and running in production, you should remove this
|
---|
150 | option.
|
---|
151 |
|
---|
152 | Another method for changing the storage engine is described in
|
---|
153 | AlterModelOnSyncDB_.
|
---|
154 |
|
---|
155 | .. _AlterModelOnSyncDB: http://code.djangoproject.com/wiki/AlterModelOnSyncDB
|
---|
156 |
|
---|