[categories] [index] [all (527)] [latest]

1. Add a fulltext index

   mysql> ALTER TABLE my_table ADD FULLTEXT(my_col);
   

   This doesn't work on InnoDB tables, but only on MyIsam ones.

   SELECT *
   FROM my_table
   WHERE MATCH (my_col)
   AGAINST('my_text');
   

2. Add a unicity constraint on a column

   mysql> ALTER TABLE nm_newsitem ADD CONSTRAINT unique_link UNIQUE (link(255));
   

3. Add an index

   mysql> ALTER TABLE my_table ADD INDEX(my_col);
   

4. Analyze a table

   mysql> ANALYZE TABLE my_table;
   

5. Choose the database

   mysql> USE my_db;
   

6. Connect to the server

   $ mysql -u user_name -p
   

7. Describe the database structure

   mysql> DESCRIBE my_table;
   

8. Display the list of tables

   mysql> SHOW TABLES;
   

9. Export a database

   $ mysqldump --opt --user=username --password database > dumpfile.sql
   
   $ mysqldump --user my_user --password=my_password database > dumpfile.sql
   

10. Export the structure of a database

    $ mysqldump --no-data --opt --user=root --password news_memory > tables.sql
    

11. Import a database

    $ mysql --user=username --password database < dumpfile.sql
    

12. Insert data

    mysql> INSERT INTO my_table VALUES ('','France','fr');
    

13. Minimal secure

    $ /usr/local/mysql/bin/mysqladmin -u root password xxxxxxxx
    

14. Modify data

    mysql> UPDATE my_table SET my_table.my_row_1 = 'Lausanne' WHERE my_table.my_row_2 = 2338;
    

15. Optimize table

    mysql> OPTIMIZE TABLE my_table;
    

16. Reset incrementation counter

    ALTER TABLE my_table AUTO_INCREMENT=1;
    

    The value is the one that will be used next.

17. Search / replace in a field

    update <table> set <champ> = replace(<champ>,'<avant>','<apres>');
    

18. Start the server

    $ sudo mysqld_safe --user=root &
    

19. Stop the server

    $ mysqladmin -u root --password=xxxxxxxx shutdown
    

20. Use the former password system

    Since version 4.1, MySQL stores password in a new way.

    Previous clients can't authenticate anymore.

    As a workaround, you can update user passwords in mysql.user table:

    mysql> UPDATE mysql.user SET password = OLD_PASSWORD('password') WHERE user = 'xxx' and host = 'xxx';
    mysql> FLUSH PRIVILEGES;
    

21. Use UTF-8 fields

    Since MySQL 4.1 :

    mysql> ALTER TABLE ma_table MODIFY ma_colonne CHAR(150) CHARACTER SET utf8;