Ticket #2635: mysql.txt

File mysql.txt, 5.4 KB (added by Andy Dustman <farcepest@…>, 15 years ago)

Some notes on using MySQL with Django. (disregard the previous HTML file, this is the original)

Line 
1========================
2MySQL Database Reference
3========================
4
5MySQL Notes
6===========
7
8Django expects the database to support transactions, referential integrity,
9and Unicode (UTF-8). Fortunately MySQL_ has all these features as available as
10far back as 3.23. While it may be possible to use 3.23 or 4.0, you will probably
11have less trouble if you use 4.1 or 5.0.
12
13MySQL-4.1
14---------
15
16MySQL-4.1_ has greatly improved support for character sets. It is possible to
17set different default character sets on the database, table, and column. Previous
18versions have only a server-wide character set setting. It's also the first version
19where the character set can be changed on the fly. 4.1 also has support for views,
20but these are not currently used by Django.
21
22MySQL-5.0
23---------
24
25MySQL-5.0_ adds the ``information_schema`` database, which contains detailed
26data on all database schema. This is used for Django's ``inspectdb`` feature,
27when it is available. 5.0 also has support for stored procedures, but these
28are 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
34Storage Engines
35---------------
36
37MySQL has several `storage engines`_ (previously called table types). You can
38change the default storage engine in the server configuration.
39
40The default one is MyISAM_. The main drawback of MyISAM is that it does not
41currently have support for transactions or foreign keys. On the plus side, it
42is currently the only engine that supports full-text indexing and searching.
43
44The InnoDB_ engine is fully transactional and supports foreign key references.
45
46The BDB_ engine, like InnoDB, is also fully transactional and supports foreign
47key references. However, it's use seems to be somewhat deprecated.
48
49`Other storage engines`_, including SolidDB_ and Falcon_, are on the horizon.
50For 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
60MySQLdb Notes
61=============
62
63`MySQLdb`_ is the Python interface to MySQL. 1.2.1 is the first version which
64has support for MySQL-4.1 and newer. If you are trying to use an older version
65of MySQL, then 1.2.0 *may* work for you.
66
67.. _MySQLdb: http://sourceforge.net/projects/mysql-python
68
69Creating your database
70----------------------
71
72You can `create your database`_ using the command-line tools and this SQL::
73
74  CREATE DATABASE <dbname> CHARACTER SET utf8;
75 
76This 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
80Connecting to the database
81--------------------------
82
83Refer to the `settings documentation`_.
84
85Connection 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
92In other words, if you set the name of the database in ``DATABASE_OPTIONS``,
93this will take precedence over ``DATABASE_NAME``, which would override
94anything in a `MySQL option file`_.
95
96Here'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
111There are several other MySQLdb connection options which may be useful, such
112as ``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
119Creating your tables
120--------------------
121
122When Django generates the schema, it doesn't specify a storage engine, so they
123will be created with whatever default `storage engine`__ your database server
124is configured for. The easiest solution is to set your database server's default
125storage engine to the desired engine.
126
127__ `storage engines`_
128
129If you are using a hosting service and can't change your server's default
130storage engine, you have a couple of options.
131
132After the tables is created, all that is needed to convert it to a new storage
133engine (such as InnoDB) is::
134 
135  ALTER TABLE <tablename> ENGINE=INNODB;
136
137With a lot of tables, this can be tedious.
138
139Another option is to use the ``init_command`` option for MySQLdb prior to
140creating your tables::
141
142  DATABASE_OPTIONS = {
143      ...
144      init_command = "SET storage_engine=INNODB",
145      ...
146      }
147
148This sets the default storage engine upon connecting to the database. After
149your tables are set up and running in production, you should remove this
150option.
151
152Another method for changing the storage engine is described in
153AlterModelOnSyncDB_.
154
155.. _AlterModelOnSyncDB: http://code.djangoproject.com/wiki/AlterModelOnSyncDB
156
Back to Top