| PURGE BINARY LOGS BEFORE '2021-06-25 00:00:00';
|
https://snipt.net/embed/cadd029c1890864b638dd9bef556129a/
/raw/cadd029c1890864b638dd9bef556129a/
cadd029c1890864b638dd9bef556129a
text
Text only
1
2024-04-27T22:27:00
True
False
False
Jun 24, 2021 at 07:45 PM
/api/public/snipt/117/
purge-binary-logs
<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>PURGE BINARY LOGS BEFORE '2021-06-25 00:00:00';
</span></pre></div>
</td></tr></table>
mysql
| SELECT
SUM(base.size) AS `Size in MB`
FROM(
SELECT
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size
FROM information_schema.TABLES
) AS base
|
https://snipt.net/embed/97488ca92ded3bb5e00eb6f4ac978f17/
/raw/97488ca92ded3bb5e00eb6f4ac978f17/
97488ca92ded3bb5e00eb6f4ac978f17
text
Text only
7
2024-05-08T00:05:41
True
False
False
Apr 17, 2021 at 09:37 PM
/api/public/snipt/116/
get-total-size-of-db
<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>
<a href="#L-6">6</a>
<a href="#L-7">7</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>SELECT
</span><span id="L-2"><a name="L-2"></a> SUM(base.size) AS `Size in MB`
</span><span id="L-3"><a name="L-3"></a>FROM(
</span><span id="L-4"><a name="L-4"></a> SELECT
</span><span id="L-5"><a name="L-5"></a> ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size
</span><span id="L-6"><a name="L-6"></a> FROM information_schema.TABLES
</span><span id="L-7"><a name="L-7"></a>) AS base
</span></pre></div>
</td></tr></table>
mysql
| select count(*) from members as m where date(CONVERT_TZ(m.created_at,'GMT','America/Los_Angeles')) = date(CONVERT_TZ(now(),'GMT','America/Los_Angeles'))
|
https://snipt.net/embed/941235bcc8e346718e4aa614b692402e/
/raw/941235bcc8e346718e4aa614b692402e/
941235bcc8e346718e4aa614b692402e
text
Text only
1
2024-05-02T18:41:59
True
False
False
Sep 02, 2019 at 06:16 AM
/api/public/snipt/108/
timezone-adjusted-span-query
<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>select count(*) from members as m where date(CONVERT_TZ(m.created_at,'GMT','America/Los_Angeles')) = date(CONVERT_TZ(now(),'GMT','America/Los_Angeles'))
</span></pre></div>
</td></tr></table>
mysql
| SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
|
https://snipt.net/embed/43bf75d0b92ac72e6b762eb8579e0c57/
/raw/43bf75d0b92ac72e6b762eb8579e0c57/
43bf75d0b92ac72e6b762eb8579e0c57
text
Text only
6
2024-05-02T22:56:32
True
False
False
/api/public/snipt/65/
get-table-size
<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>
<a href="#L-6">6</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>SELECT
</span><span id="L-2"><a name="L-2"></a> table_schema as `Database`,
</span><span id="L-3"><a name="L-3"></a> table_name AS `Table`,
</span><span id="L-4"><a name="L-4"></a> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
</span><span id="L-5"><a name="L-5"></a>FROM information_schema.TABLES
</span><span id="L-6"><a name="L-6"></a>ORDER BY (data_length + index_length) DESC;
</span></pre></div>
</td></tr></table>
mysql, mysqli, sql
| select count(*) as totalsent, sum(opened is not null) as opened, round((sum(opened is not null)/count(*))*100) openperc, sum(clicked is not null) as clicked, round((sum(clicked is not null)/sum(opened is not null))*100) clickperc
|
https://snipt.net/embed/3ec2b73c60eba7468418c6d08ad753ee/
/raw/3ec2b73c60eba7468418c6d08ad753ee/
3ec2b73c60eba7468418c6d08ad753ee
text
Text only
2
2024-05-02T15:32:51
True
False
False
Apr 20, 2018 at 01:19 PM
/api/public/snipt/56/
sum-of-not-null-values
<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>select count(*) as totalsent, sum(opened is not null) as opened, round((sum(opened is not null)/count(*))*100) openperc, sum(clicked is not null) as clicked, round((sum(clicked is not null)/sum(opened is not null))*100) clickperc
</span></pre></div>
</td></tr></table>
mysql, mysqli, sql
| mysqldump --routines newsroom > newsroom-alpha-2017-11-15.sql
|
https://snipt.net/embed/c94d576f20b3f94a10a2afc53ce41291/
/raw/c94d576f20b3f94a10a2afc53ce41291/
c94d576f20b3f94a10a2afc53ce41291
text
Text only
1
2024-05-07T23:38:07
True
False
False
Nov 15, 2017 at 04:45 PM
/api/public/snipt/16/
mysqldump
<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>mysqldump --routines newsroom > newsroom-alpha-2017-11-15.sql
</span></pre></div>
</td></tr></table>
mysql
| -- Drop the constraint if it exists
set @var=if((SELECT true FROM information_schema.TABLE_CONSTRAINTS WHERE
CONSTRAINT_SCHEMA = DATABASE() AND
TABLE_NAME = 'bluebookfacts' AND
CONSTRAINT_NAME = 'fk_bb_log_facts' AND
CONSTRAINT_TYPE = 'FOREIGN KEY') = true,'ALTER TABLE bluebook DROP FOREIGN KEY fk_bb_log_facts', 'select 1');
prepare stmt from @var;
execute stmt;
deallocate prepare stmt;
|
Expand
Collapse
(9 lines)
https://snipt.net/embed/09601d166c92af3b299cd0cc0a3a8154/
/raw/09601d166c92af3b299cd0cc0a3a8154/
09601d166c92af3b299cd0cc0a3a8154
text
Text only
9
2024-05-02T09:42:49
True
False
False
Feb 08, 2017 at 03:16 PM
/api/public/snipt/11/
drop-the-constraint-if-it-exists
<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>
<a href="#L-6">6</a>
<a href="#L-7">7</a>
<a href="#L-8">8</a>
<a href="#L-9">9</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>-- Drop the constraint if it exists
</span><span id="L-2"><a name="L-2"></a>set @var=if((SELECT true FROM information_schema.TABLE_CONSTRAINTS WHERE
</span><span id="L-3"><a name="L-3"></a> CONSTRAINT_SCHEMA = DATABASE() AND
</span><span id="L-4"><a name="L-4"></a> TABLE_NAME = 'bluebookfacts' AND
</span><span id="L-5"><a name="L-5"></a> CONSTRAINT_NAME = 'fk_bb_log_facts' AND
</span><span id="L-6"><a name="L-6"></a> CONSTRAINT_TYPE = 'FOREIGN KEY') = true,'ALTER TABLE bluebook DROP FOREIGN KEY fk_bb_log_facts', 'select 1');
</span><span id="L-7"><a name="L-7"></a>prepare stmt from @var;
</span><span id="L-8"><a name="L-8"></a>execute stmt;
</span><span id="L-9"><a name="L-9"></a>deallocate prepare stmt;
</span></pre></div>
</td></tr></table>
mysql
| `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
|
https://snipt.net/embed/f99dbed8067d0e301a66b517e6eac47a/
/raw/f99dbed8067d0e301a66b517e6eac47a/
f99dbed8067d0e301a66b517e6eac47a
text
Text only
1
2024-05-04T15:22:43
True
False
False
May 30, 2022 at 04:34 AM
/api/public/snipt/147048/
default-timestamp
<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> `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
</span></pre></div>
</td></tr></table>
mysql, sql
