snipt

Ctrl+h for KB shortcuts

MySQL

Import Excel CSV into mysql table

1
2
3
4
5
LOAD DATA LOCAL INFILE 'file.cvs' INTO TABLE tablename
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(field1, field2, field3)
https://snipt.net/embed/b45c79fed7778c5162ab2733e55610f7/
/raw/b45c79fed7778c5162ab2733e55610f7/
b45c79fed7778c5162ab2733e55610f7
mysql
MySQL
6
2019-08-13T07:04:39
True
False
False
/api/public/snipt/4923/
import-excel-csv-into-mysql-table
<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">LOAD</span> <span class="n">DATA</span> <span class="n">LOCAL</span> <span class="k">INFILE</span> <span class="s1">&#39;file.cvs&#39;</span> <span class="k">INTO</span> <span class="k">TABLE</span> <span class="n">tablename</span> </span><span id="L-2"><a name="L-2"></a><span class="n">FIELDS</span> <span class="k">TERMINATED</span> <span class="k">BY</span> <span class="s1">&#39;,&#39;</span> </span><span id="L-3"><a name="L-3"></a><span class="k">ENCLOSED</span> <span class="k">BY</span> <span class="s1">&#39;&quot;&#39;</span> </span><span id="L-4"><a name="L-4"></a><span class="k">LINES</span> <span class="k">TERMINATED</span> <span class="k">BY</span> <span class="s1">&#39;\r\n&#39;</span> </span><span id="L-5"><a name="L-5"></a><span class="p">(</span><span class="n">field1</span><span class="p">,</span> <span class="n">field2</span><span class="p">,</span> <span class="n">field3</span><span class="p">)</span> </span></pre></div> </td></tr></table>
csv, excel, mysql

MySQL

Return text between delimiters in MySQL

1
2
3
4
5
SELECT 
  SUBSTR(column, 
    LOCATE(':',column)+1, 
      (CHAR_LENGTH(column) - LOCATE(':',REVERSE(column)) - LOCATE(':',column))) 
FROM table
https://snipt.net/embed/56ea271da0d3b88e9426456bbc66b14e/
/raw/56ea271da0d3b88e9426456bbc66b14e/
56ea271da0d3b88e9426456bbc66b14e
mysql
MySQL
5
2019-08-15T22:56:16
True
False
False
/api/public/snipt/3322/
return-text-between-delimiters-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><span id="L-2"><a name="L-2"></a> <span class="nf">SUBSTR</span><span class="p">(</span><span class="k">column</span><span class="p">,</span> </span><span id="L-3"><a name="L-3"></a> <span class="nf">LOCATE</span><span class="p">(</span><span class="s1">&#39;:&#39;</span><span class="p">,</span><span class="k">column</span><span class="p">)</span><span class="o">+</span><span class="mi">1</span><span class="p">,</span> </span><span id="L-4"><a name="L-4"></a> <span class="p">(</span><span class="nf">CHAR_LENGTH</span><span class="p">(</span><span class="k">column</span><span class="p">)</span> <span class="o">-</span> <span class="nf">LOCATE</span><span class="p">(</span><span class="s1">&#39;:&#39;</span><span class="p">,</span><span class="nf">REVERSE</span><span class="p">(</span><span class="k">column</span><span class="p">))</span> <span class="o">-</span> <span class="nf">LOCATE</span><span class="p">(</span><span class="s1">&#39;:&#39;</span><span class="p">,</span><span class="k">column</span><span class="p">)))</span> </span><span id="L-5"><a name="L-5"></a><span class="k">FROM</span> <span class="k">table</span> </span></pre></div> </td></tr></table>
mysql

MySQL

show state & thread ID's of MySQL processes

SHOW PROCESSLIST;
https://snipt.net/embed/48249b7e42ff1283a6efb0212f0f78df/
/raw/48249b7e42ff1283a6efb0212f0f78df/
48249b7e42ff1283a6efb0212f0f78df
mysql
MySQL
1
2019-07-31T23:35:09
True
False
False
/api/public/snipt/1349/
show-state-thread-ids-of-mysql-processes
<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">SHOW</span> <span class="n">PROCESSLIST</span><span class="p">;</span> </span></pre></div> </td></tr></table>
mysql, optimization

Bash

mysql dump into file

mysqldump -u USER -p DATABASE > filename.sql
https://snipt.net/embed/4ab6543aeaabfd991684f9a173a932cf/
/raw/4ab6543aeaabfd991684f9a173a932cf/
4ab6543aeaabfd991684f9a173a932cf
bash
Bash
1
2019-08-06T17:57:50
True
False
False
/api/public/snipt/292/
mysql-dump-into-file
<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 -u USER -p DATABASE &gt; filename.sql </span></pre></div> </td></tr></table>
backup, mysql