blog

MySQL to MongoDB – An Admin Cheat Sheet

Onyancha Brian Henry

Published

Most software applications nowadays involve some dynamic data storage for extensive future reference in the application itself. We all know data is stored in a database which falls into two categories that are: Relational and Non-relational DBMS.

Your choice of selection from these two will fully depend on your data structure, amount of data involved, database performance and scalability.

Relational DBMS store data in tables in terms of rows such that they use Structured Querying Language (SQL) making them a good choice for applications involving several transactions. They include MySQL, SQLite, and PostgreSQL.

On the other hand, NoSQL DBMS such as MongoDB are document-oriented such that data is stored in collections in terms of documents. This gives a greater storage capacity for a large set of data hence a further advantage in scalability.

In this blog we are assuming you have a better knowledge for either MongoDB or MySQL and hence would like to know the correlation between the two in terms of querying and database structure.

Below is a cheat sheet to further familiarize yourself with the querying of MySQL to MongoDB.

MySQL to MongoDB Cheat Sheet – Terms

MySQL Terms MongoDB Terms Explanation
Table Collection This is the storage container for data that tends to be similar in the contained objects.
Row Document Defines the single object entity in the table for MySQL and collection in the case of MongoDB.
Column Field For every stored item, it has properties which are defined by different values and data types. In MongoDB, documents in the same collection, may have different fields from each other. In MySQL, every row must be defined with the same columns from the existing ones.
Primary key Primary key Every stored object is identified with a unique field value in the case of MongoDB we have _id field set automatically whereas in MySQL you can define your own primary key which is incremental as you create new rows.
Table Joins Embedding and linking documents Connection associated with an object in a different collection/table to data in another collection/table.
where $match Selecting data that matches criteria.
group $group Grouping data according to some criteria.
drop $unset Removing a column/field from a row/document/
set $set Setting the value of an existing column/field to a new value.
Severalnines
 
Become a MongoDB DBA – Bringing MongoDB to Production
Learn about what you need to know to deploy, monitor, manage and scale MongoDB

Schema Statements

MySQL Table Statements MongoDB Collection Statements Explanation

The database and tables are created explicitly through the PHP admin panel or defined within a script i.e

Creating a Database

CREATE DATABASE database_name

Creating a table

CREATE TABLE users (
    id MEDIUMINT NOT NULL
        AUTO_INCREMENT,
    UserId Varchar(30),
    Age Number,
    Gender char(1),
    Name VarChar(222),
    PRIMARY KEY (id)
)

The database can be created implicitly or explicitly. Implicitly during the first document insert the database and collection are created as well as an automatic _id field being added to this document.

db.users.insert( {
    UserId: "user1",
    Age: 55,
    Name: "Berry Hellington",
    Gender: "F",
 } )

You can also create the database explicitly by running this comment in the Mongo Shell

db.createCollection("users")

In MySQL, you have to specify the columns in the table you are creating as well as setting some validation rules like in this example the type of data and length that goes to a specific column. In the case of MongoDB, it is not a must to define neither the fields each document should hold nor the validation rules the specified fields should hold.

However, in MongoDB for data integrity and consistency you can set the validation rules using the JSON SCHEMA VALIDATOR

Dropping a table

DROP TABLE users
db.users.drop()

This are statements for deleting a table for MySQL and collection in the case of MongoDB.

Adding a new column called join_date

ALTER TABLE users ADD join_date DATETIME

Removing the join_date column if already defined

ALTER TABLE users DROP COLUMN join_date DATETIME

Adding a new field called join_date

db.users.updateMany({},{$set:{‘join_date’: new Date()})

This will update all documents in the collection to have the join date as the current date.

Removing the join_date field if already defined

db.users.updateMany({},{$unset:{‘join_date’: “”})

This will remove the join_date field from all the collection documents.

Altering the structure of the schema by either adding or dropping a column/field.

Since the MongoDB architecture does not strictly enforce on the document structure, documents may have fields different from each other.

Creating an index with the UserId column ascending and Age descending

CREATE INDEX idx_UserId_asc_Age_desc
ON users(UserId)

Creating an index involving the UserId and Age fields.

db.users.ensureIndex( { UserId: 1, Age: -1 } )

Indices are generally created to facilitate the querying process.

INSERT INTO users(UserId,
                  Age,
                  Gender)
VALUES ("user1",
        25,
        "M")
db.users.insert( {
       UserId: "bcd001",
       Age: 25,
       Gender: "M",
     Name: "Berry Hellington",
} )

Inserting new records.

DELETE FROM users
WHERE Age = 25
db.users.deleteMany( { Age = 25 } )

Deleting records from the table/collection whose age is equal to 25.

DELETE FROM users
db.users.deleteMany({})

Deleting all records from the table/collection.

SELECT * FROM users
db.users.find()

Returns all records from the users table/collection with all columns/fields.

SELECT id, Age, Gender FROM users
db.users.find(
   { },
   { Age: 1, Gender: 1 }
)

Returns all records from the users table/collection with Age, Gender and primary key columns/fields.

SELECT  Age, Gender FROM users
db.users.find(
   { },
 { Age: 1, Gender: 1,_id: 0}
)

Returns all records from the users table/collection with Age and Gender columns/fields. The primary key is omitted.

SELECT * FROM users WHERE Gender = “M”
db.users.find({ Gender: "M"})

Returns all records from the users table/collection whose Gender value is set to M.

SELECT Gender FROM users WHERE Age = 25
db.users.find({ Age: 25}, { _id: 0, Gender: 1})

Returns all records from the users table/collection with only the Gender value but whose Age value is equal to 25.

SELECT * FROM users WHERE Age = 25 AND Gender = ‘F’
db.users.find({ Age: 25, Gender: "F"})

Returns all records from the users table/collection whose Gender value is set to F and Age is 25.

SELECT * FROM users WHERE  Age != 25
db.users.find({ Age:{$ne: 25}})

Returns all records from the users table/collection whose Age value is not equal to 25.

SELECT * FROM users WHERE Age = 25 OR Gender = ‘F’
db.users.find({$or:[{Age: 25, Gender: "F"}]})

Returns all records from the users table/collection whose Gender value is set to F or Age is 25.

SELECT * FROM users WHERE Age > 25
db.users.find({ Age:{$gt: 25}})

Returns all records from the users table/collection whose Age value is greater than 25.

SELECT * FROM users WHERE Age <= 25
db.users.find({ Age:{$lte: 25}})

Returns all records from the users table/collection whose Age value is less than or equal to 25.

SELECT Name FROM users WHERE Name like "He%"
db.users.find(
  { Name: /He/ }
)

Returns all records from the users table/collection whose Name value happens to have He letters.

SELECT * FROM users WHERE Gender = ‘F’ ORDER BY id ASC
db.users.find( { Gender: "F" } ).sort( { $natural: 1 } )

Returns all records from the users table/collection whose Gender value is set to F and sorts this result in the ascending order of the id column in case of MySQL and time inserted in the case of MongoDB.

SELECT * FROM users WHERE Gender = ‘F’ ORDER BY id DESC
db.users.find( { Gender: "F" } ).sort( { $natural: -1 } )

Returns all records from the users table/collection whose Gender value is set to F and sorts this result in the descending order of the id column in case of MySQL and time inserted in the case of MongoDB.

SELECT COUNT(*) FROM users
db.users.count()

or

db.users.find().count()

Counts all records in the users table/collection.

SELECT COUNT(Name) FROM users
db.users.count({Name:{ $exists: true }})

or

db.users.find({Name:{ $exists: true }}).count()

Counts all records in the users table/collection who happen to have a value for the Name property.

SELECT * FROM users LIMIT 1
db.users.findOne()

or

db.users.find().limit(1)

Returns the first record in the users table/collection.

SELECT * FROM users WHERE Gender = ‘F’ LIMIT 1
db.users.find( { Gender: "F" } ).limit(1)

Returns the first record in the users table/collection that happens to have Gender value equal to F.

SELECT * FROM users LIMIT 5 SKIP 10
db.users.find().limit(5).skip(10)

Returns the five records in the users table/collection after skipping the first five records.

UPDATE users SET Age = 26 WHERE age > 25
db.users.updateMany(
  { age: { $gt: 25 } },
  { $set: { Age: 26 } }
)

This sets the age of all records in the users table/collection who have the age greater than 25 to 26.

UPDATE users SET age = age + 1
db.users.updateMany(
  {} ,
  { $inc: { age: 1 } }
)

This increases the age of all records in the users table/collection by 1.

UPDATE users SET age = age - 1
WHERE id = 1
db.users.updateMany(
  {} ,
  { $inc: { age: -1 } }
)

This decrements the age of the first record in the users table/collection by 1.

To manage MySQL and/or MongoDB centrally and from a single point, visit: https://severalnines.com/product/clustercontrol.

Subscribe below to be notified of fresh posts