-
Joining Collections in MongoDB Queries using $lookup
Note: This only works in MongoDB 3.2 or later, be sure to update if you need this functionality! In situations where you have an ObjectID in a collection and you want it resolved by MongoDB during your query, you can accomplish this with aggregate and lookup. Let’s say we had two collections: insuranceClaim and insuranceProvider.…
-
Deleting Rows From a Table Referenced in the Query
If you do a fair amount of SQL, then every now and then, you’ll likely run into a situation where you need to delete something from a table; but need to also reference that table in the query as well. The trouble is MySQL won’t let you do this, in many circumstances. Fortunately, there is…
-
Using MySQL Queries to Dump/Format Data to Delimited Files
It is often useful to utilize MySQL queries, to export data from tables then write this data into files. MySQL offers the ability to customize many of the details of these exports, which should cover most situations. In this example, we are selecting data from a table, applying some formatting and writing it to a…
-
Efficiently Loading Paginated Results From MySQL with Just One Query Per Page
There are many situations in which pagination of query results is very useful, especially for performance optimization. In most of these kinds of situations, the paginating of results requires you to determine the total number of results, so the application knows the number of pages available. The most common way to do this, is to…
-
Bash Shell Script to Convert All MySQL Tables to use the Innodb Engine
The following, when run in the Bash shell, will convert the engine for all of your MySQL tables to InnoDB. mysql -B -N -e “SHOW TABLES” -u –password= | while read table; \ do \ echo “+ Converting Table $table”; \ mysql -B -N -e “alter table $table engine=innodb” -u –password= ; \ done; Often,…
-
Locating Duplicate Entries In A MySQL Table
If you don’t have unique indexes on your table, it is likely that you will occasionally have entries that are duplicated. This can often happen because of a software bug or possibly a user error. Some applications even choose not to have unique indexes for performance reasons; though this happens at the cost of data…
-
Eliminating Duplicate Records From MySQL Tables
Anyone who works with database driven development to any extent, will occasionally run into a situation where duplicate information is added to their database. I have personally run into such a problem on many occasions since I started working with database driven software. Being able to quickly undo data corruption is extremely important in production…