Database schemas Databases are present in almost all kinds of application that need to store information in some form or the other. Web applications like blogs, cms, social networking sites or business applications etc all have a database along with the code. The database design or schema determines how the tables and their relationships are constructed. Database design is a crucial component in the overall efficiency of a database application. In this series of posts, [...]

It is possible to export the contents of a table directly to csv format from within mysql. It is useful when migrating large tables. CSV export import is much faster for large tables compared to mysqldump/mysql command. To generate csv from the table use the following command Make sure that the path to the output file is writable by mysql. If the output file already exists, it would say ERROR 1086 (HY000): File ‘/tmp/result.csv’ already [...]

There are situations when we need an autoincrement field to reset every month or so. Example : Invoice Numbers : Month of october Month of november and so on. Groupwise autoincrement P1 , P2 , Q1 , Q2 , Q3 , Q4 , P3 , R1 , P4 … Myisam table has a feature where , if a primary key contains 2 columns where one is an autoincrement and other is a varchar , then [...]

If the mysql server is on ubuntu and other machines on LAN need to access it directly then : 1. Edit /etc/mysql/my.cnf and comment the line bind-address=some_ip 2. Restart mysql , sudo /etc/init.d/mysql restart 2. Open phpmyadmin , go to privileges , and add the ip address with the necessary privileges. Now the new ip can access the mysql database of this machine over LAN. Last Updated On : 2nd October 2011

information_schema The following query will list out the foreign keys in mysql. It finds it out from the information_schema database. The output is a clean table listing out all foreign keys from all databases +———————–+————-+ | foreign key | references | +———————–+————-+ | orders.client_id | clients.id | | line_items.order_id | orders.id | | line_items.product_id | products.id | +———————–+————-+ Source To view the foreign keys of only a particular database use WHERE table_schema = ‘database_name’ Basically [...]

The innodb storage engine supports foreign keys in Mysql. To create foreign keys in phpmyadmin : 1. Convert both tables into innodb, if they are not already. 2. View the structure of the table which will have a foreign key. Make the referencing field an INDEX. 3. Now come back to structure view and click Relation view. In the Relation view page the field (which was made an INDEX) can be made a foreign key [...]

Views and mysqldump A little back I tried to backup a database from my webhost and restore it on my localhost mysql. It had a few views. The import of the sql file seemed to work fine. But on accessing the views in phpmyadmin mysql gave the error : #1449 – There is no ‘projects’@'localhost’ registered The problem is with a security feature for views. It defines which user created a view and which user [...]

While creating a table a particular column / field can be made autoincrement as : The value of an autoincrement column increments automatically with every insert and doesnt need to be specified in the insert command. It is 1 + the value of the same field in the previous row. Now data can be inserted as : Note that the value for the id field (which is an autoincrement field) is not specified. It fills [...]