query – PlusMagi's Blog By Pitt Phunsanit

SQL Server: Kill process ของข้าให้หมด

มีแก้งานที่เขียน query ได้โหดยูเครนมาก (รัสเซียได้พิสูจน์ให้เห็นแล้วว่ากาก) ใช้ cpu สูงมาก ใช้ ram เกือบหมดเม็ค แล้วมีคนใช้งานอยู่พร้อม ๆ กัน T-T เลยหา script มาจัดการดีด connection ที่ใช้อยู่ในเครื่องตัวเองทิ้งยกเว้นที่ใช้อยู่ใน sql server management studio (ssms) และโปรแกรม app ที่กำลังเขียนอยู่

DECLARE @SqlCmd VARCHAR(1000)
  
	,@HostName VARCHAR(100)-- Set the hostname name from which to kill the connections

  
	--SET @HostName = 'Pitt_P'

SET @HostName = HOST_NAME()

SET @SqlCmd = ''  SELECT @Sqlcmd = @SqlCmd + CHAR(13) + CHAR(10) + 'KILL ' + convert(CHAR(10), spid) + ' '
 
FROM master.dbo.sysprocesses
 
WHERE hostname = @HostName
 
	AND DBID <> 0
 
	AND spid <> @@spid PRINT @sqlcmd EXEC(@Sqlcmd)
 
GO

แก้จากต้นแบบ Kill all processes associated with a hostname โดยดึงข้อมูล HOST HOST_NAME แทนที่จะใส่ชื่อเครื่องตัวเองไป

MySQL: ใช้ตัวแปร variable

การใช้ตัวแปรใน mysql จะช่วยให้เขียน sql query ได้สั้นและเข้าใจได้ง่ายขึ้น อย่างในกรณีที่นำค่าจากการคำนวณมาค่าหนึ่ง แล้วต้องนำไปใช้แสดงผลใน column อื่นๆ อีกครั้ง เช่น การนำผลที่ได้มาแสดงเป็นช่วงแบบตามขั้นบันได แบ่งช่วงวันที่ออกเป็นกลุ่มๆ หรือการตัดเกรด

สมมุติว่า ต้องตัดเกรดให้เด็กตามช่วงคะแนน โดย

accumulatedScore: คะแนนเก็บ เต็ม 100 คะแนนจะมีน้ำหนักเป็น 50%
midtermScore: คะแนนกลางภาค เต็ม 100 คะแนนจะมีน้ำหนักเป็น 20%
finalScore: คะแนนสอบปลายภาค เต็ม 100 คะแนนจะมีน้ำหนัก 30%

Codeigniter reused active record query

ในการเขียน query บางครั้งก็ต้องเขียนคิวรี่ที่ต่างกันเล็กๆ น้อยๆ เช่น ต้องแบ่ง list ข้อมูลออกเป็นหน้าๆ หรือที่เรียกกันว่า pagination จะมี 2 query ที่ต้องใช้คือ query ที่นับ ข้อมูลที่มีทั้งหมด

SELECT `d`.`district_id`,
       `d`.`enable`,
       `d`.`district_code`,
       `d`.`district_name`,
       `p`.`province_name`
FROM   `district` AS `d`
       LEFT JOIN `province` AS `p`
              ON `d`.`province_id` = `p`.`province_id`

และ query ที่ดึงข้อมูลเฉพาะช่วงที่กำหนด

SELECT `d`.`district_id`,
       `d`.`enable`,
       `d`.`district_code`,
       `d`.`district_name`,
       `p`.`province_name`
FROM   `district` AS `d`
       LEFT JOIN `province` AS `p`
              ON `d`.`province_id` = `p`.`province_id`
LIMIT  0, 10

จะเห็นว่า ต่างกันแค่ LIMIT 0, 10 เท่านั้น แต่เพราะว่า ไม่ควร query ข้อมูล ที่ไม่ได้ใช้ จึงเปลี่ยน

SELECT COUNT(district_id) AS recordsTotal
FROM   `district` AS `d`
       LEFT JOIN `province` AS `p`
              ON `d`.`province_id` = `p`.`province_id`

แก้ select ให้เป็น SELECT COUNT(DISTRICT_ID) AS recordsTotal แทนที่จะเลือกหลายๆ column หลาย row มาเพื่อแค่จะนับว่ามีข้อมูลกี่ record แค่นั้นเอง

ยังยุ่งไม่พอ เมื่อทำเป็น data table หรือ grid จะมีเงื่อนไขอย่างเช่น filter ข้อมูล หรือ order ข้อมูลใหม่ตาม column ต่างๆ ที่ user ปลับเปลี่ยนเวลาใช้งาน ถ้าต้องเขียนเงื่อนใขเพิ่ม ถ้าเป็น query string อย่าง DISTRICT_ID = ‘xxx’ ก็พอจะเก็บในตัวแปรแล้วเอามาต่อกันได้ แต่ถ้าเขียนเป็น active record ก็ลำบากมากที่ต้องมาเขียนใหม่หลายๆจุด แต่ Codeigniter มีวิธี reused active record มาใช้ใหม่ได้ลดความซับซ้อนลงไปได้เยอะเลย

วิธีการคือเขียน query ส่วนที่ใช้ ร่วมกัน ระหว่าง ->start_cache(); และ ->stop_cache(); จากนั้นเขียน ส่วนอื่นเพิ่มเข้าไป เป็นวิธีที่ฉลาดมากๆ

<?php

class DatabasecachingModel extends CI_Model
{

    public function __construct()
    {
        // Call the CI_Model constructor
        parent::__construct();

        $this->db = $this->load->database('db', true);
    }

    public function getPagination()
    {
        $post = $this->input->post();

        // Turn caching on
        $this->db->start_cache();

        $this->db->from('district AS d');
        $this->db->join('province AS p', 'd.PROVINCE_ID = p.PROVINCE_ID', 'left');

        if (isset($post['DISTRICT_ID'])) {
            $this->db->where('DISTRICT_ID', $post['DISTRICT_ID']);
        }

        if (isset($post['PROVINCE_ID'])) {
            $this->db->where('PROVINCE_ID', $post['PROVINCE_ID']);
        }

        $this->db->stop_cache();

        $this->db->select('COUNT(DISTRICT_ID) AS recordsTotal');
        $recordsTotal = $this->db->get()->row()->recordsTotal;

        echo '<br><br> query string for count = ' . $this->db->last_query();
        echo '<br> records total = ' . $recordsTotal;

        $this->db->select('d.DISTRICT_ID, d.enable, d.DISTRICT_CODE, d.DISTRICT_NAME, p.province_NAME');
        $this->db->limit(10, 0);
        $results = $this->db->get()->result_array();

        echo '<br><br> query string for results = ' . $this->db->last_query();
        echo '<pre>', print_r($results, true), '<pre>';

    }

}

Mysql: log query

debug ระบบที่ใช้ mysql แต่หาไม่เจอว่าข้อมูลเปลี่ยนที่จุดไหน เลยต้อง log ทุกๆ sql query ที่ส่งไปให้ mysql server

เปิดไฟล์ config ของ mysql ขึ้นมา โดยแต่ละระบบจะไม่เหมือนกัน

Ubuntu/Debian

/etc/mysql/my.cnf

wamp

c:\wamp\bin\mysql\mysqlx.y.z\my.ini

Windows

c:\ProgramData\MySQL\MySQL Server 5.x

xampp

c:\xampp\mysql\bin\my.ini

เพิ่ม

# log all query
general_log = on
general_log_file = C:\xampp\logs_mysql\general_log.txt
log_output = file

สร้างไฟล์ C:\xampp\logs_mysql\all.txt ขึ้นมา ตัว mysql จะไม่สร้างให้อัตโนมัติถ้าไม่มีไฟล์นี้
restart mysql service ใหม่

ถ้าเปลี่ยน config ให้ log_output = table log จะถูกบันทึกไว้ในตาราง general_log ใน database mysql แทน

ควรเปิดใช้เมื่อจำเป็นเท่านั้น ไม่ควรเปิดทิ้งไว้เพราะจะทำให้ mysql ทำงานช้าลง

อ่านเพิ่มเติม

ส่งค่าตัวแปรในเช็กบ็อกซ์โดย jQuery

เขียนระบบ update ข้อมูลโดยใช้ ajax แต่ปัญหาคือ ใน form นี้มันมี input อยู่หลายตัว และเพราะว่าต้องการแค่ input ที่ชื่อ items[] ตัวเดียวเท่านั้น ถ้าส่ง data ไปโดยใช้ jQuery โดยปกติจะส่งค่าไปโดยใช้ .serialize() หรือ .serializeArray() ก็จะส่งตัวแปรอื่นๆ ที่ไม่จำเป็นติดไปด้วย จนทำให้ url ยาวจนเกิน limit ทำให้ต้องหาวิธีส่งไปเฉพาะตัวที่ใช้จริงๆ เท่านั้น

ตัวอย่างการส่งค่าไปในแบบ array

<!doctype html>
<html>

<head>
    <meta charset="utf-8">
    <title>checkbox to array</title>
</head>

<body>
    <ul>
        <li>
            <input name="items[]" type="checkbox" value="c1">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c2">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c3">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c4">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c5">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c6">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c7">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c8">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c9">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c10">
        </li>
    </ul>
    <div id="result"></div>
    <button id="sendBtn" type="submit">Send</button>
    <script src="jquery-3.1.1.min.js"></script>
    <script>
    $(function() {

        $('#sendBtn').click(function() {
            var checkboxs = $('input[name="items\\[\\]"]:checked');

            alert('checked ' + checkboxs.length + ' items');

            var values = checkboxs.map(function() {
                    return $(this).val();
                })
                .get();

            var params = {
                "id": 24,
                "items": values,
            };
            $.ajax({
                "data": params,
                "success": function(data) {
                    $('#result').html(data);
                },
                "type": "GET",
                "url": "processing.php",
            });

        });

    });
    </script>
</body>

</html>

ตัวอย่างการส่งค่าไปในแบบ string

<!doctype html>
<html>

<head>
    <meta charset="utf-8">
    <title>checkbox to string</title>
</head>

<body>
    <ul>
        <li>
            <input name="items[]" type="checkbox" value="c1">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c2">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c3">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c4">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c5">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c6">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c7">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c8">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c9">
        </li>
        <li>
            <input name="items[]" type="checkbox" value="c10">
        </li>
    </ul>
    <div id="result"></div>
    <button id="sendBtn" type="submit">Send</button>
    <script src="jquery-3.1.1.min.js"></script>
    <script>
    $(function() {

        $('#sendBtn').click(function() {
            var checkboxs = $('input[name="items\\[\\]"]:checked');

            alert('checked ' + checkboxs.length + ' items');

            var values = checkboxs.map(function() {
                    return $(this).val();
                })
                .get()
                .join();

            alert('values = ' + values);

            var encoded = encodeURIComponent(values);

            alert('URL encoded = ' + encoded);

            var decoded = decodeURIComponent(encoded);

            alert('URL decoded = ' + decoded);

            $.ajax({
                "data": 'id=24&items=' + encoded,
                "success": function(data) {
                    $('#result').html(data);
                },
                "type": "GET",
                "url": "processing.php",
            });

        });

    });
    </script>
</body>

</html>

ตัวอย่างการนำค่าไปเขียนเป็น sql query

<dl>
  <dt>method="GET"</dt>
  <dd><?=print_r($_GET, true);?></dd>
  <dt>method="POST"</dt>
  <dd><?=print_r($_POST, true);?></dd>
</dl>
<?php
if (is_array($_REQUEST['items'])) {
    echo '<br>send items by array';
    $where = "WHERE id IN('" . implode("', '", $_REQUEST['items']) . "')";
} else {
    echo '<br>send items by string';
    $where = "WHERE id IN('" . str_replace(',', "', '", $_REQUEST['items']) . "')";
}

$query = "SELECT *
FROM table_name
$where;";

echo '<br>example query = ' . $query;

เลือกใช้การส่งแบบสตริงหรืออาร์เรย์ ก็แล้วแต่ความสดวก ที่ต้องนำไป loop อีกหรือแค่ใช้ในการสร้างเอสคิวแอลคิวรีอย่างเดียว

จัดคิวรี่ใหม่ใน SQL Server

ถ้าเจอ code ที่ดูยุ่งเหยิงอ่านยาก แค่มองก็พาลจะทำให้เป็นไมเกรน จะเรียกว่า spaghetti code ในฐานข้อมูลก็จะมี query ที่ซ้อนกันหลายชั้น หรือคิวรี่ที่ออกมาจากการที่บรรดาเฟรมเวิร์คต่างๆ สร้างมาให้อัตโนมัติ จะเขียนติดกันเป็นวัชพีช กว่าจะรู้ว่ามันเป็นอะไร ก็ต้องเอามันมาจัดซะก่อน หลายปีก่อนจะใช้บริการ online SQL Formatter อย่าง SQL Formatter for SQL Serve

จนได้มาเจอกับ ชายผู้น่าสงสารแต่รวยน้ำใจ Poor Man’s T-SQL Formatter ความสามารถของ library ชุดนี้คือ จัดรูปแบบ T-SQL ใหม่ โดยสามารถกำหนดเงื่อนไข วรรค์ตอน ขึ้นบรรทัดใหม่ ส่วนต่างๆ ได้เอง และที่สำคัญสามารถใช้เป็นปลั๊กอินได้ในหลายโปรแกรม

SQL Server Management Studio (SSMS)
Visual Studio
Notepad++ โปรแกรมฟรีก็ใช้ได้
Command-line ถ้ามีให้จัดเยอะๆ ก็ไม่เป็นปัญหา
WinMerge โปรแกรมเทียบโค้ทที่ดีที่สุด
ท้ายสุดบนเว็บอย่าง Poor SQL

ตัวอย่างเช่น ถ้าลงใน SQL Server Management Studio โดยใช้ SqlFormatterSSMSAddIn จะมีเมนูเพิ่มขึ้นมาในเมนู tools สามารถคลิกเพื่อปรับรูปแบบ query ที่ใช้อยู่ตอนนั้นได้เลย ถ้าไม่ชอบหรือคิดว่าจัดอีกแบบดีกว่า สามารถคลิกอีกเมนูปรับรูปแบบได้ตามต้องการเลย ของเค้าดีจริงๆ ไม่น่าเชื่อว่าฟรีทุกอย่างเลย

สามารถดูวิธีติดตั้งได้จาก

Windows: install error 0x800f0954

update ข้อมูลใน MySQL แบบหลายตาราง

กำลังทำ presashop ตัวใหม่อยู่ จำเป็นต้องใส่ต้อง copy feature จากภาษาหนึ่งไปอีกภาษาหนึ่งสำหรับ product 1,600 รายการ ถ้าเลือกใส่ใน backend ปกติถึงทำกัน 3 คนมือก็หงิกอยู่ดี (ยังไม่ต้องคิดว่าถ้าทำพลาดละ) และเพราะ prestashop เก็บข้อมูลโดยแยกแต่ละภาษา ออกไปอีกตารางจึ้งต้องเขียน sub query ตามแบบ
SQL Update column values using subquery
query ของผมเป็น

UPDATE `feature_value_lang` AS target INNER JOIN (
    SELECT `id_feature_value` ,`value`FROM `feature_value_lang`
    WHERE `id_lang` = 1
) AS source ON target.`id_feature_value` = source.`id_feature_value`
AND  target.`id_lang` = 3
SET target.`value` = source.`value`

copy ข้อมูลใน MySQL

บางครั้งต้องการข้อมูลจำนวนมาก เพื่อ test บางอย่าง เช่น ระบบแบ่งหน้า โชคดีที่มายเอสคิวแอลสามารถ copy มาจาก record อื่นๆได้ เช่น

INSERT INTO tableName (col1, col2, col3, ...)
SELECT col1, col2, col3, ... FROM tableName
  WHERE primarykey = 1

สมมุติจะคัดลอกตาราง `cds` ในดาต้าเบส cdcol ที่เป็นตัวอย่างติด xampp มา ถ้าไม่มีสร้างใหม่ได้จาก query

CREATE TABLE IF NOT EXISTS `cds` (
  `titel` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `interpret` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `jahr` int(11) DEFAULT NULL,
`id` bigint(20) unsigned NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

INSERT INTO `cds` (`titel`, `interpret`, `jahr`, `id`) VALUES
('Beauty', 'Ryuichi Sakamoto', 1990, 1),
('Goodbye Country (Hello Nightclub)', 'Groove Armada', 2001, 4),
('Glee', 'Bran Van 3000', 1997, 5);

ALTER TABLE `cds`
 ADD PRIMARY KEY (`id`);

เราจะ copy เพิ่มโดยคิวรี่

INSERT INTO `cds`(`titel`, `interpret`, `jahr`)
SELECT `titel`, `interpret`, `jahr`
FROM `cds`
WHERE `id` = 5

สังเกตุ จะไม่เลือก ฟิลด์ id เพราะ field มันเป็น primary key หลังจากรันคิวรี่จะเห็นว่าข้อมูลเพิ่มขึ้นมาใหม่เหมือนข้อมูลใน id = 5 ทุกอย่างยกเว้น id

คุมการแสดงผลตามเวลา

ตัวอย่าง query กำหนดเวลาแสดงผลของ mysql ให้แสดง ไม่แสดงตามเวลาที่กำหนด โดยยูเซอร์เลือกที่จะใส่ไม่ใส่ time, expire, start, end ก็ได้ เช่น จะแสดงหลังวันที่ xxx ไปตลอดกาลก็ใส่แต่วันที่เริ่มต้น จะแสดงจนถึงวันที่เท่าไหร่ก็ใส่เฉพาะวันที่สิ้นสุด จะแสดงในช่วงเวลาก็ใส่ทั้งคู่ กลับกัน ไม่ใส่ทั้งคู่ก็เหมือนไม่กำหนดเงือนไขอะไรเลย

SELECT *,
       CASE
         WHEN datePublish != '0000-00-00'
              AND dateExpire != '0000-00-00' THEN
         IF(datePublish <= Curdate()
            AND dateExpire >= Curdate(), true, false)
         WHEN datePublish != '0000-00-00' THEN
         IF(datePublish <= Curdate(), true, false
         )
         WHEN dateExpire != '0000-00-00' THEN
         IF(dateExpire >= Curdate(), true, false)
         ELSE true
       END AS published
FROM pp_contents
HAVING published = true

จัด format SQL ใน SQL SEVER

ใน SQL SEVER บางครั้งเราอาจจะต้องแก้ sql statement ยาวๆ ซ้อนกันหลายๆชั้น ถ้าไปจัด format ก่อนโดยใช้ เว็บที่ให้บริการ sql formatter เช่น freeformatter ,SQLFormat Online SQL formatting service จะอ่านคำสั่งง่ายขึ้น

แต่น่าจะดีกว่าถ้าสามารถจัด format ใน SQL Server Management Studio ได้โดยตรงทำได้โดยการโหลด Plug-in Poor Man’s T-SQL Formatter จากเว็บ Architect Shack โดยหลังจากติดตั้งแล้วจะมี เมนู Format T-SQL Code เพิ่มขึ้นมา ถ้าไม่ใช้รูปแบบที่คุ้นเคยสามารถกำหนดรูปแบบที่ถนัดได้ในเมนู T-SQL Formatting Options

นอกจากนี้ ยังมี plug in สำหรับ notepage++ ,WinMerge อีกนะครับ เข้าใจ programmer ,DBA จริงๆ

สามารถดูวิธีติดตั้งได้จาก https://pitt.plusmagi.com/windows-install-error-0x800f0954/

Tag Archive query