snipt

Ctrl+h for KB shortcuts

MySQL

Find MySQL Collation and Charsets

1
2
3
4
USE database_name;

SHOW VARIABLES LIKE "character_set_database";
SHOW VARIABLES LIKE "collation_database";
https://snipt.net/embed/fdaa71b9921a6e4991919d5e578a1791/
/raw/fdaa71b9921a6e4991919d5e578a1791/
fdaa71b9921a6e4991919d5e578a1791
mysql
MySQL
4
2019-07-11T17:21:11
True
False
False
/api/public/snipt/41726/
find-mysql-collation-and-charsets
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">USE</span> <span class="n">database_name</span><span class="p">;</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="k">SHOW</span> <span class="n">VARIABLES</span> <span class="k">LIKE</span> <span class="s2">&quot;character_set_database&quot;</span><span class="p">;</span> </span><span id="L-4"><a name="L-4"></a><span class="k">SHOW</span> <span class="n">VARIABLES</span> <span class="k">LIKE</span> <span class="s2">&quot;collation_database&quot;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
charset, collation, mysql, utf8

SQL

Increment date by 14 days

UPDATE events SET date_starts = DATE_ADD(date_starts,INTERVAL 14 DAY) WHERE event_id = 3;
https://snipt.net/embed/ccc397d6860ef356f43744d5b10c5f14/
/raw/ccc397d6860ef356f43744d5b10c5f14/
ccc397d6860ef356f43744d5b10c5f14
sql
SQL
1
2019-07-14T08:25:20
True
False
False
/api/public/snipt/38558/
increment-date-by-14-days
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">UPDATE</span> <span class="n">events</span> <span class="k">SET</span> <span class="n">date_starts</span> <span class="o">=</span> <span class="n">DATE_ADD</span><span class="p">(</span><span class="n">date_starts</span><span class="p">,</span><span class="nb">INTERVAL</span> <span class="mi">14</span> <span class="k">DAY</span><span class="p">)</span> <span class="k">WHERE</span> <span class="n">event_id</span> <span class="o">=</span> <span class="mi">3</span><span class="p">;</span> </span></pre></div> </td></tr></table>
date, mysql, sql

MySQL

calculating age from date of birth in MySQL

1
2
3
4
5
-- Version 1:
DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthdate, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthdate, '00-%m-%d')) AS age

-- Version 2:
EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),birthdate))))+0 AS age
https://snipt.net/embed/ab36407fbbf6db41c66466107242f5e7/
/raw/ab36407fbbf6db41c66466107242f5e7/
ab36407fbbf6db41c66466107242f5e7
mysql
MySQL
5
2019-06-23T13:43:19
True
False
False
/api/public/snipt/37038/
calculating-age-from-date-of-birth-in-mysql
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">-- Version 1:</span> </span><span id="L-2"><a name="L-2"></a><span class="nf">DATE_FORMAT</span><span class="p">(</span><span class="nf">NOW</span><span class="p">(),</span> <span class="s1">&#39;%Y&#39;</span><span class="p">)</span> <span class="o">-</span> <span class="nf">DATE_FORMAT</span><span class="p">(</span><span class="n">birthdate</span><span class="p">,</span> <span class="s1">&#39;%Y&#39;</span><span class="p">)</span> <span class="o">-</span> <span class="p">(</span><span class="nf">DATE_FORMAT</span><span class="p">(</span><span class="nf">NOW</span><span class="p">(),</span> <span class="s1">&#39;00-%m-%d&#39;</span><span class="p">)</span> <span class="o">&lt;</span> <span class="nf">DATE_FORMAT</span><span class="p">(</span><span class="n">birthdate</span><span class="p">,</span> <span class="s1">&#39;00-%m-%d&#39;</span><span class="p">))</span> <span class="k">AS</span> <span class="n">age</span> </span><span id="L-3"><a name="L-3"></a> </span><span id="L-4"><a name="L-4"></a><span class="c1">-- Version 2:</span> </span><span id="L-5"><a name="L-5"></a><span class="nf">EXTRACT</span><span class="p">(</span><span class="kt">YEAR</span> <span class="k">FROM</span> <span class="p">(</span><span class="nf">FROM_DAYS</span><span class="p">(</span><span class="nf">DATEDIFF</span><span class="p">(</span><span class="nf">NOW</span><span class="p">(),</span><span class="n">birthdate</span><span class="p">))))</span><span class="o">+</span><span class="mi">0</span> <span class="k">AS</span> <span class="n">age</span> </span></pre></div> </td></tr></table>
mysql

MySQL

Drop tables with a certain prefix

1
2
3
4
-- This will compose an SQL query that you can run afterwards:
SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';
https://snipt.net/embed/ab590505c05b5327738ec81b444a6bc7/
/raw/ab590505c05b5327738ec81b444a6bc7/
ab590505c05b5327738ec81b444a6bc7
mysql
MySQL
4
2019-07-15T18:47:46
True
False
False
/api/public/snipt/35894/
drop-tables-with-a-certain-prefix
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">-- This will compose an SQL query that you can run afterwards:</span> </span><span id="L-2"><a name="L-2"></a><span class="k">SELECT</span> <span class="nf">CONCAT</span><span class="p">(</span> <span class="s1">&#39;DROP TABLE &#39;</span><span class="p">,</span> <span class="nf">GROUP_CONCAT</span><span class="p">(</span><span class="n">table_name</span><span class="p">)</span> <span class="p">,</span> <span class="s1">&#39;;&#39;</span> <span class="p">)</span> </span><span id="L-3"><a name="L-3"></a> <span class="k">AS</span> <span class="n">statement</span> <span class="k">FROM</span> <span class="n">information_schema</span><span class="p">.</span><span class="kp">tables</span> </span><span id="L-4"><a name="L-4"></a> <span class="k">WHERE</span> <span class="n">table_schema</span> <span class="o">=</span> <span class="s1">&#39;database_name&#39;</span> <span class="k">AND</span> <span class="n">table_name</span> <span class="k">LIKE</span> <span class="s1">&#39;myprefix_%&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
mysql

MySQL

Finding duplicate records in mysql

1
2
3
4
5
SELECT title, COUNT(title) AS num_found
FROM articles
GROUP BY title
HAVING num_found > 1
ORDER BY num_found DESC;
https://snipt.net/embed/fda42d21d2b6ec448a154963753734d3/
/raw/fda42d21d2b6ec448a154963753734d3/
fda42d21d2b6ec448a154963753734d3
mysql
MySQL
5
2019-07-06T08:55:19
True
False
False
/api/public/snipt/22053/
finding-duplicate-records-in-mysql
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="n">title</span><span class="p">,</span> <span class="nf">COUNT</span><span class="p">(</span><span class="n">title</span><span class="p">)</span> <span class="k">AS</span> <span class="n">num_found</span> </span><span id="L-2"><a name="L-2"></a><span class="k">FROM</span> <span class="n">articles</span> </span><span id="L-3"><a name="L-3"></a><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">title</span> </span><span id="L-4"><a name="L-4"></a><span class="k">HAVING</span> <span class="n">num_found</span> <span class="o">&gt;</span> <span class="mi">1</span> </span><span id="L-5"><a name="L-5"></a><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">num_found</span> <span class="k">DESC</span><span class="p">;</span> </span></pre></div> </td></tr></table>
count, group, mysql, select

Bash

Database dump and import from the commandline

1
2
3
4
5
#dump
mysqldump db_name --user=db_user -p > backup.sql

#import
mysql --user=username -p db_name < backup.sql
https://snipt.net/embed/55ee90c093ef42f638112fe20e7af750/
/raw/55ee90c093ef42f638112fe20e7af750/
55ee90c093ef42f638112fe20e7af750
bash
Bash
5
2019-07-11T09:47:26
True
False
False
/api/public/snipt/12915/
dump-a-database-from-the-commandline
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">#dump</span> </span><span id="L-2"><a name="L-2"></a>mysqldump db_name --user<span class="o">=</span>db_user -p &gt; backup.sql </span><span id="L-3"><a name="L-3"></a> </span><span id="L-4"><a name="L-4"></a><span class="c1">#import</span> </span><span id="L-5"><a name="L-5"></a>mysql --user<span class="o">=</span>username -p db_name &lt; backup.sql </span></pre></div> </td></tr></table>
backup, dump, mysql, mysqldump