Disabling foreign key checks in MySQL is usefull when you are dealing with tables that use foreign keys (InnoDB engine). You can not delete (drop) multiple tables, a parent table or a child table until you disable foreign key checks four your current database. The sql command to disable foreign key checks is: [sql]SET FOREIGN_KEY_CHECKS …
Tag: MySql
Permanent link to this article: https://blog.openshell.in/2012/09/disable-foreign-key-checks-in-mysql/
Aug 02
MySQL: Get total number of rows when using LIMIT
A SELECT statement may include a LIMIT clause to restrict the number of rows return by the MySQL server. In some cases, it is desirable to know how many rows the SELECT statement would have returned without the LIMIT. To obtain the row count by include SQL_CALC_FOUND_ROWS option in the SELECT statement, and then execute …
Permanent link to this article: https://blog.openshell.in/2012/08/mysql-get-total-number-of-rows-when-using-limit/
Sep 19
PHP MYSQL ORDER BY Multiple Columns
If you want to Order 2 or more columns in SQL Query. When ordering by more than one column, the second column is only used if the values are identical with the onces in the first column. Example [sql] ‘Order by Column ORDER BY COLUMN1, COLUMN2 [/sql] Order 2 or more columns with different orders …
Permanent link to this article: https://blog.openshell.in/2011/09/php-mysql-order-by-multiple-columns/
Dec 29
MySQL Subquery Optimization
Never use a subquery inside a WHERE field IN(…) the SQL query. It will slow down the process. Slow: SELECT * FORM table1 WHERE field1 IN (SELECT field2 FROM table2 GROUP BY field1) Instead of using a subquery inside an IN(…), use INNER JOIN query. It will speed up the process, but in IN(…) query …
Permanent link to this article: https://blog.openshell.in/2010/12/mysql-subquery-optimization/
Dec 10
Importing data from CSV File
Here is an example to importing data from csv file into MySQL table. LOAD DATA INFILE “/home/mysql/data/file_name.csv” INTO TABLE table_name FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\\n’;
Permanent link to this article: https://blog.openshell.in/2010/12/importing-data-from-csv-file/
Dec 10
Find duplicate repords in Table
Here is an example to find duplicate records from the table. select address, count(address) as cnt from mailing_list group by address having cnt > 1 order by cnt;
Permanent link to this article: https://blog.openshell.in/2010/12/find-duplicate-repords-in-table/
Dec 10
Export a table into csv format
Here is an example to export data from MySQL into CSV File Format. SELECT * INTO OUTFILE ‘/tmp/file_name.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘n’ FROM table_name;
Permanent link to this article: https://blog.openshell.in/2010/12/export-a-table-into-csv-format/
Dec 10
Fields Other than the PK Need to be Indexed
The Table Primary Key (PK) is automatically an index. Indexes should be used whenever a relationship needs to be established between two tables using a field other than the PK E.g. both fields included in the ON table1.field1=table2.field3 clause. Making both fields indexes allows MySQL to JOIN the two tables much more efficiently and much …
Permanent link to this article: https://blog.openshell.in/2010/12/fields-other-than-the-pk-need-to-be-indexed/
Dec 02
Escape String Literals for SQL
To run a SQL query with text data containing single quotes ‘ as well as other SQL reserved punctuations, and to prevent SQL injections, you will always want to escape the text values before using them in a SQL query. mysql_real_escape_string() calls MySQL’s library function mysql_real_escape_string, which prepends backslashes to the following characters: x00, n, …
Permanent link to this article: https://blog.openshell.in/2010/12/escape-string-literals-for-sql/
- 1
- 2