mysql – Page 292 – Tarik Billa

Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

September 9, 2022 by Tarik

Differences KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don’t enforce any restraints on your data so they are used only for access – for quickly reaching certain ranges of … Read more

How to skip certain database tables with mysqldump?

September 9, 2022 by Tarik

You can use the –ignore-table option. So you could do mysqldump -u USERNAME -pPASSWORD DATABASE –ignore-table=DATABASE.table1 > database.sql There is no whitespace after -p (this is not a typo). To ignore multiple tables, use this option multiple times, this is documented to work since at least version 5.0. If you want an alternative way to … Read more

MySQL: Grant all privileges on database

September 9, 2022 by Tarik

GRANT ALL PRIVILEGES ON mydb.* TO ‘myuser’@’%’ WITH GRANT OPTION; This is how I create my “Super User” privileges (although I would normally specify a host). IMPORTANT NOTE While this answer can solve the problem of access, WITH GRANT OPTION creates a MySQL user that can edit the permissions of other users. The GRANT OPTION … Read more

How do I see all foreign keys to a table or column?

September 8, 2022 by Tarik

For a Table: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘<database>’ AND REFERENCED_TABLE_NAME = ‘<table>’; For a Column: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘<database>’ AND REFERENCED_TABLE_NAME = ‘<table>’ AND REFERENCED_COLUMN_NAME = ‘<column>’; Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

Create new user in MySQL and give it full access to one database

September 8, 2022 by Tarik

Try this to create the user: CREATE USER ‘user’@’hostname’; Try this to give it access to the database dbTest: GRANT ALL PRIVILEGES ON dbTest.* To ‘user’@’hostname’ IDENTIFIED BY ‘password’; If you are running the code/site accessing MySQL on the same machine, hostname would be localhost. Now, the break down. GRANT – This is the command … Read more

How to get size of mysql database?

September 8, 2022 by Tarik

Run this query and you’ll probably get what you’re looking for: SELECT table_schema “DB Name”, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) “DB Size in MB” FROM information_schema.tables GROUP BY table_schema; This query comes from the mysql forums, where there are more comprehensive instructions available.

Find duplicate records in MySQL

September 8, 2022 by Tarik

The key is to rewrite this query so that it can be used as a subquery. SELECT firstname, lastname, list.address FROM list INNER JOIN (SELECT address FROM list GROUP BY address HAVING COUNT(id) > 1) dup ON list.address = dup.address;

When to use single quotes, double quotes, and backticks in MySQL

September 8, 2022 by Tarik

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the … Read more

‘IF’ in ‘SELECT’ statement – choose output value based on column values

September 8, 2022 by Tarik

SELECT id, IF(type=”P”, amount, amount * -1) as amount FROM report See http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html. Additionally, you could handle when the condition is null. In the case of a null amount: SELECT id, IF(type=”P”, IFNULL(amount,0), IFNULL(amount,0) * -1) as amount FROM report The part IFNULL(amount,0) means when amount is not null return amount else return 0.