MySQL table snippets and fixes to common problems

Unlocking the full potential of MySQL can transform the way you manage and interact with your database. Whether you’re diving into SQL for the first time or seeking to refine your data manipulation skills, mastering MySQL table snippets is crucial.

These snippets serve as building blocks for efficient and effective database management, ensuring that your queries are both powerful and precise.

In this article, you’ll find a treasure trove of practical SQL query examples and table creation scripts. You’ll learn how to craft robust table structures, optimize your database schema, and utilize advanced features like stored procedures and triggers.

Each snippet is designed to streamline your workflow, enhance data retrieval, and bolster database performance.

By the end, you’ll not only understand how to implement these snippets but also appreciate their impact on schema design and SQL query optimization. Ready to elevate your MySQL expertise?

Table of Contents

MySQL table snippets

Match a string at the beginning of a string

SELECT 'Test' REGEXP '^The'; -- 0 SELECT 'The Test' REGEXP '^The'; -- 1

If a name is not prefixed with ‘The ‘ then add it

UPDATE [table] SET Name = CONCAT('The ', TRIM(Name)) WHERE Name NOT REGEXP '^The'

Copy a column from one table to another

INSERT INTO [table] ([column]) SELECT [column] FROM [table]

Remove all whitespace

UPDATE [table] SET [column] = REPLACE([column], ' ', '')

(this is BASH) drop all tables in a database

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

Change to collation of a table and all of its existing columns

alter table [table] convert to character set utf8 collate utf8_general_ci;

MySQL Find and Replace

UPDATE files SET filepath = REPLACE(filepath,'path/to/search','path/to/replace');

Selecting a Random Row In MySQL

SELECT column FROM table ORDER BY RAND() LIMIT 1

Create CSV from MySQL

SELECT a,b,c FROM my_table INTO OUTFILE '/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Find duplicate records

SELECT id, COUNT(*) as n FROM my_table GROUP BY id HAVING n >1;

Correcting wrong character encoding in MySQL data

UPDATE table SET column=CONVERT(CONVERT(CONVERT(column USING latin1) USING binary) using utf8);

Load CSV back into mySQL

LOAD DATA local INFILE 'unique.csv' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (col1, col2, col3)

Drupal 6.x Find PHP code in database

select nid, vid from node_revisions where format in (select format from filters where module = 'php' and delta = 0); select cid, nid from comments where format in (select format from filters where module = 'php' and delta = 0); select bid, info from boxes where format in (select format from filters where module = 'php' and delta = 0);

Generate random string in MySQL

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6) AS randomstring

Calculate Age from Date Of Birth

SELECT FLOOR(DATEDIFF(CURRENT_DATE(), dob) / 365);

Find & Replace

UPDATE table_name SET column_name = REPLACE(column_name, 'http:\/\/oldcontent.com', 'http:\/\/newcontent.com');

Select between two dates

SELECT * FROM files LEFT JOIN (dates) ON files.uploaded_id = dates.id WHERE date BETWEEN '2009-01-01' AND '2009-12-31'

Backup MySQL Database to SQL File with mysqldump

mysqldump --user=username --password=password --opt --add-drop-database --databases databasename > ~/path/to/file.sql

how to calculate last 7 days

WHERE mydatefld >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)

mysql dump

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

Mysql replace

# Replace string in all records from a table UPDATE table_name SET column_name = REPLACE( column_name, '/dev', '' ); # Display a modified record set SELECT REPLACE( column_name, '/dev', '' ) FROM table_name;

Convert timezone to local

SELECT CONVERT_TZ(mydatefield,'US/Pacific','CET') FROM mytable;

Search and Replace on Multiple Rows in SQL

UPDATE wp_posts SET post_content = REPLACE(post_content, 'staging.server.com', 'www.productionserver.com');

How to Save results from MySQL into an external file

echo "SELECT columns FROM table_name WHERE whatever='something'" | /path/to/mysql -uUSERNAME -pPASSWORD DATABASENAME > /tmp/outfile.txt;

Insert or update if unique key already exist

INSERT INTO `tableName` VALUES (?,?,?,'-1',?,'0') ON DUPLICATE KEY UPDATE `id` = ?, `foo` = ?, `bar` = ?

mySQL – database backup commands

-- dump mySQL database to file: VAR=$(date +Ã¢ï¿½ï¿½%y-%m-%dÃ¢ï¿½ï¿½); mysqldump -v -uUSERNAME -pPASSWORD -hlocalhost DATABASENAME > /BACKUPDIR/$VAR-DATABASENAME.sql ;

-- dump mySQL database to file and then move it: VAR=$(date +Ã¢ï¿½ï¿½%y-%m-%dÃ¢ï¿½ï¿½); mysqldump -v -uUSERNAME -pPASSWORD -hlocalhost DATABASENAME > /BACKUPDIR/$VAR-DATABASENAME.sql ; mv /BACKUPDIR/DATABASENAME.sql /OTHERBACKUPDIR/$VAR-DATABASENAME.sql ;

MySql import

mysql -u [username] -p [dbname] < [filename].sql

find duplicate records

select address, count(address) as cnt from mailing_list group by address having cnt > 1 order by cnt;

MySQL Find and Replace

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find this string', 'replace found string with this string');

Change Drupal file directory path

UPDATE FILES SET filepath = REPLACE(filepath, 'sites/default/files/old/','sites/default/files/new/');

Find all tables in MySQL database containing specific column names

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('columnA','ColumnB') AND TABLE_SCHEMA='YourDatabase';

Find values that occur exactly once in table

SELECT email FROM users GROUP BY email HAVING ( COUNT(email) = 1 )

MySql script to find invalid email addresses

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'

MySQL Age Calculation

DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(DOB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(DOB, '00-%m-%d')) >= 65

Left Joins to link three or more tables

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid where dom.rid is NULL;

MySQL REGEX match comma-delimitted list

WHERE id REGEXP '(,|^){$this->id}(,|$)'

Get random record based on weight

SELECT * FROM Table ORDER BY Rand( ) * ( 1 / Weight

FAQs about MySQL table snippets

What is a MySQL table snippet, and how does it differ from a regular table?

A table snippet in MySQL is a portion of a table that only contains a particular set of data. It features columns and rows, much like a typical table, however, it only contains a subset of the data from the original database.

A SELECT statement that defines the desired columns and rows from the original table is used to construct a snippet. This method allows you to extract specific data for analysis or reporting, making it a valuable technique for dbt testing and data management.

How do I create a basic MySQL table?

To create a basic MySQL table, use the CREATE TABLE statement. Define your columns, data types, and any constraints like primary keys. Here’s a simple example:

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

What are common MySQL data types?

MySQL supports various data types to accommodate different types of data. Common ones include INT for integers, VARCHAR for variable-length strings, DATE for dates, and FLOAT for floating-point numbers. Choosing the right data type ensures efficient data storage and retrieval.

How do I add a primary key to a MySQL table?

A primary key uniquely identifies each record in a table. To add one, use the PRIMARY KEY constraint within your CREATE TABLE statement. For example:

CREATE TABLE products (
product_id INT AUTO_INCREMENT,
product_name VARCHAR(255),
PRIMARY KEY (product_id)
);

How can I optimize MySQL table performance?

To optimize MySQL table performance, use indexes, avoid redundant data, and normalize your database schema. Also, consider using the EXPLAIN command to analyze SQL query execution plans and refine your queries for better performance.

What is a foreign key in MySQL?

A foreign key establishes a relationship between two tables, ensuring referential integrity. It links a column in one table to a primary key in another. For example:

CREATE TABLE orders (
order_id INT,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);

How do I alter an existing MySQL table?

To alter an existing MySQL table, use the ALTER TABLE statement. This allows you to add, modify, or drop columns and constraints. For instance, to add a new column:

ALTER TABLE users ADD COLUMN age INT;

What are MySQL indexes and why are they important?

Indexes speed up data retrieval by allowing quick access to rows in a table. They are crucial for enhancing query performance. Create indexes using the CREATE INDEX command:

CREATE INDEX idx_username ON users(username);

How do I use MySQL triggers?

Triggers automatically execute predefined SQL code in response to certain events on a table. They are useful for enforcing business rules. Here’s a simple example:

CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

What are stored procedures in MySQL?

Stored procedures are precompiled collections of SQL statements stored in the database. They encapsulate complex operations and can be called repeatedly, promoting code reuse. Define a stored procedure like this:

DELIMITER //
CREATE PROCEDURE GetUserDetails()
BEGIN
SELECT * FROM users;
END //
DELIMITER ;

Conclusion

Exploring examples of MySQL table snippets unveils the power and flexibility inherent in SQL and database management. These snippets, from table creation scripts to complex stored procedures, are indispensable tools for crafting efficient, robust database schemas.

Integrating primary keys, foreign keys, and indexes enhances data integrity and performance.

By delving into MySQL triggers and stored procedures, we unlock automated operations and reusable code blocks that streamline workflows and ensure consistency.

Leveraging these techniques not only boosts query performance but also fortifies the overall structure of your database.

If you enjoyed reading this article about MySQL table snippets, you should also read these: