読者です 読者をやめる 読者になる 読者になる

MySQL で UPSERT する時に考えること

SQL

MySQL で UPSERT をするとき、REPLACE を使うべきか? INSERT のオプション ON DUPLICATE KEY UPDATE を使うべきか?

REPLACE は、キー重複する行を削除してから INSERT することを考えると、ON DUPLICATE KEY UPDATE の方が
更新する列を指定できるから良いのか悪いのか?結論が出ない。

大量の行をUPSERTしたい場合を考慮したい。。

サンプル。。

DELIMITER //
DROP TABLE IF EXISTS test.projects
//
CREATE TABLE test.projects (
  key1           INT NOT NULL
, target_name    VARCHAR(32)  NOT NULL 
, memo           VARCHAR(100) DEFAULT NULL
, PRIMARY KEY (key1)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
//
DELIMITER ;

INSERT INTO projects (key1, target_name, memo )VALUES( 1, 'A', 'あ' ),( 2, 'B', 'い' );
commit;

REPLACE を書く場合。。。

REPLACE projects (key1, target_name, memo 
)VALUES( 1, 'AA', 'AAA' ), ( 2, 'BB', 'BBB' ), ( 3, 'CC', 'CCC' )

ON DUPLICATE KEY UPDATEを書く場合、、

INSERT projects (key1,  target_name, memo 
)VALUES( 1, 'AA', 'AAA' ), ( 2, 'BB', 'BBB' ), ( 3, 'CC', 'CCC' )
ON DUPLICATE KEY UPDATE
  target_name = VALUES(target_name)
, memo = VALUES(memo)

どちらも、

+------+-------------+------+
| key1 | target_name | memo |
+------+-------------+------+
|    1 | AA          | AAA  |
|    2 | BB          | BBB  |
|    3 | CC          | CCC  |
+------+-------------+------+

という結果を得られるけど、、

この ON DUPLICATE KEY UPDATE で指定する列を、target_name だけにするなら、

   ON DUPLICATE KEY UPDATE target_name = VALUES(target_name)

だけにするなら、、

+------+-------------+------+
| key1 | target_name | memo |
+------+-------------+------+
|    1 | AA          | あ   |
|    2 | BB          | い   |
|    3 | CC          | CCC  |
+------+-------------+------+

という結果になる。

日付期間重複の抽出

Java

昨日、書いた日付期間のインターフェース DateSpan
これを使って、

BiConsumer の 期間が重複している重複日と重複した要素のリストを処理するメソッドを用意します。

public static void booklist(List<? extends DateSpan> list, BiConsumer<LocalDate, List<? super DateSpan>> bookConsumer){
   AtomicInteger i = new AtomicInteger(0);
   Map<LocalDate, List<? super DateSpan>> map = new HashMap<>();
   list.stream().forEach(e->{
      list.subList(i.incrementAndGet(), list.size()).stream()
      .filter(t->e.isBook(t.start(), t.end()))
      .forEach(t->{
         SimpleEntry<LocalDate, LocalDate> key
          = new SimpleEntry<>(e.start().compareTo(t.start()) <= 0 && t.start().compareTo(e.end()) <= 0 ? t.start() : e.start()
               , e.start().compareTo(t.end()) <= 0 && t.end().compareTo(e.end()) <= 0 ? t.end() : e.end() );
         for(LocalDate dt=key.getKey(); dt.compareTo(key.getValue()) <= 0;dt=dt.plusDays(1)){
            List<? super DateSpan> plist = map.containsKey(dt) ? map.get(dt) : new ArrayList<>();
            plist.add(e);
            plist.add(t);
            map.put(dt, plist);
         }
      });
   });
   map.keySet().stream().sorted().forEach(k->{
      bookConsumer.accept(k, map.get(k).stream().distinct().collect(Collectors.toList()));
   });
}

使う時は、、

List<? extends DateSpan> list;

booklist(list, (k, t)->{
     // k = 重複発生日
     // t = 重複発生した日、1日に該当する要素のリスト、2個以上のリスト
});

ということです。

LocalDate の Strema 生成

Java

開始日 LocalDate と 終了日 LocalDate 、 start 、 end の Stream

int len = Period.between(start, end).getDays() + 1;
Stream<LocalDate> stream = Stream.iterate(start, t->t.plusDays(1)).limit(len);


ちょっと遊んだ、ふざけたコード

StreamSupport.stream(
  Spliterators.spliterator(Stream.iterate(start, t->t.plusDays(1)).limit(len).iterator(), len, Spliterator.ORDERED )
  , false
).forEach(e->{
  System.out.println(e);
});

DateSpan

Java

日付期間に関する処理のために。。

import java.time.LocalDate;
import java.time.Period;
import java.util.AbstractMap;
import java.util.AbstractMap.SimpleEntry;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Supplier;
import java.util.stream.Collectors;

/**
 * 日付期間インターフェース. AbstractMap.SimpleEntry<LocalDate, LocalDate>;開始日と終了日とする期間に対する日付処理
 */
@FunctionalInterface
public interface DateSpan extends Supplier<AbstractMap.SimpleEntry<LocalDate, LocalDate>>{
   /**
    * 期間内か否か?.
    * @param date 検査対象日
    * @return true=期間内である
    */
   public default boolean inRange(LocalDate date){
      AbstractMap.SimpleEntry<LocalDate, LocalDate> pair = get();
      return pair.getKey().compareTo(date) <= 0 && date.compareTo(pair.getValue()) <= 0;
   }

   /**
    * 期間と重複する?.
    * @param start 検査対象開始日
    * @param end 検査対象終了日
    * @return true=重複する
    */
   public default boolean isBook(LocalDate start, LocalDate end){
      AbstractMap.SimpleEntry<LocalDate, LocalDate> pair = get();
      return start.compareTo(pair.getValue()) <= 0 && end.compareTo(pair.getKey()) >= 0;
   }

   /**
    * 重複日数を抽出.
    * @param start 検査対象開始日
    * @param end 検査対象終了日
    * @return 重複した日数
    */
   public default long bookDays(LocalDate start, LocalDate end){
      AbstractMap.SimpleEntry<LocalDate, LocalDate> pair = get();
      if (start.compareTo(pair.getValue()) <= 0 && end.compareTo(pair.getKey()) >= 0){
         LocalDate sdate = pair.getKey().compareTo(start) <= 0 && start.compareTo(pair.getValue()) <= 0 ? start : pair.getKey();
         LocalDate edate = pair.getKey().compareTo(end) <= 0 && end.compareTo(pair.getValue()) <= 0 ? end : pair.getValue();
         return Period.between(sdate, edate).getDays() + 1;
      }
      return 0;
   }

   /**
    * 重複期間を抽出.
    * @param start 検査対象開始日
    * @param end 検査対象終了日
    * @return 重複期間の開始日と終了の AbstractMap.SimpleEntry、重複がなければ null
    */
   public default AbstractMap.SimpleEntry<LocalDate, LocalDate> bookPair(LocalDate start, LocalDate end){
      AbstractMap.SimpleEntry<LocalDate, LocalDate> pair = get();
      if (start.compareTo(pair.getValue()) <= 0 && end.compareTo(pair.getKey()) >= 0){
         return new AbstractMap.SimpleEntry<LocalDate, LocalDate>(
               pair.getKey().compareTo(start) <= 0 && start.compareTo(pair.getValue()) <= 0 ? start : pair.getKey()
               , pair.getKey().compareTo(end) <= 0 && end.compareTo(pair.getValue()) <= 0 ? end : pair.getValue());
      }
      return null;
   }

   /**
    * 期間リスト→重複期間リストの抽出.
    * @param targets 検査対象になる開始日と終了日の期間ペアのリスト
    * @return 複期間の開始日と終了の AbstractMap.SimpleEntryのリスト、重複がなければ size = 0
    */
   public default List<SimpleEntry<LocalDate, LocalDate>> bookPair(List<SimpleEntry<LocalDate, LocalDate>> targets){
      AbstractMap.SimpleEntry<LocalDate, LocalDate> pair = get();
      return targets.stream().filter(e->e.getKey().compareTo(pair.getValue()) <= 0 && e.getValue().compareTo(pair.getKey()) >= 0)
      .map(e->new AbstractMap.SimpleEntry<LocalDate, LocalDate>(
            pair.getKey().compareTo(e.getKey()) <= 0 && e.getKey().compareTo(pair.getValue()) <= 0 ? e.getKey() : pair.getKey()
            , pair.getKey().compareTo(e.getValue()) <= 0 && e.getValue().compareTo(pair.getValue()) <= 0 ? e.getValue() : pair.getValue()))
      .collect(Collectors.toList());
   }

   /**
    * 非重複期間リストを抽出.
    * @param start 検査対象開始日
    * @param end 検査対象終了日
    * @return 重複でない期間を List で抽出
    */
   public default List<AbstractMap.SimpleEntry<LocalDate, LocalDate>> unbookPairs(LocalDate start, LocalDate end){
      List<AbstractMap.SimpleEntry<LocalDate, LocalDate>> list = new ArrayList<>();
      AbstractMap.SimpleEntry<LocalDate, LocalDate> pair = get();
      if (start.compareTo(pair.getValue()) <= 0 && end.compareTo(pair.getKey()) >= 0){
         if (start.compareTo(pair.getKey()) < 0){
            list.add(new SimpleEntry<LocalDate, LocalDate>(start, pair.getKey().minusDays(1)));
         }
         if (pair.getValue().compareTo(end) < 0){
            list.add(new SimpleEntry<LocalDate, LocalDate>(pair.getValue().plusDays(1), end));
         }
      }else{
         list.add(new SimpleEntry<>(start, end));
      }
      return list;
   }

   /**
    * 期間リスト→非重複期間リストの抽出.
    * @param targets 検査対象になる開始日と終了日の期間ペアのリスト
    * @return 重複でない期間のリスト
    */
   public default List<SimpleEntry<LocalDate, LocalDate>> unbookPairs(List<SimpleEntry<LocalDate, LocalDate>> targets){
      List<AbstractMap.SimpleEntry<LocalDate, LocalDate>> list = new ArrayList<>();
      AbstractMap.SimpleEntry<LocalDate, LocalDate> pair = get();
      targets.stream().forEach(e->{
         if (e.getKey().compareTo(pair.getValue()) <= 0 && e.getValue().compareTo(pair.getKey()) >= 0){
            if (e.getKey().compareTo(pair.getKey()) < 0){
               list.add(new SimpleEntry<LocalDate, LocalDate>(e.getKey(), pair.getKey().minusDays(1)));
            }
            if (pair.getValue().compareTo(e.getValue()) < 0){
               list.add(new SimpleEntry<LocalDate, LocalDate>(pair.getValue().plusDays(1), e.getValue()));
            }
         }else{
            list.add(new SimpleEntry<>(e.getKey(), e.getValue()));
         }
      });
      return list;
   }

   public default LocalDate start(){
      return get().getKey();
   }
   public default LocalDate end(){
      return get().getValue();
   }
}

Combination の計算する程のことのない単純なペアの処理

Java

単純にリストの項目を要素2個のペアで、全組み合わせの処理をするのに、数式 nCr に相当する処理を書いていたので
沢山の処理になってしまう。

要素2個のペアの処理を全組み合わせを単純に行いたい。

サンプル

List<String> list = Arrays.asList( "a", "b", "c", "d", "e");
AtomicInteger i = new AtomicInteger(0);
list.stream().forEach(e->{
   list.subList(i.incrementAndGet(), list.size()).stream().forEach(s->{
        // ペア e と s の処理を行う。         
        System.out.println( e + " - " + s );
   });
});

結果

a - b
a - c
a - d
a - e
b - c
b - d
b - e
c - d
c - e
d - e

単に、subList を stream の処理で流してるだけだが。

1行の table (HTML) を Java で解析する手法について

Java

HTML 表(tableタグ) の1行もしくは数行の tr-td タグの文字列から単純に td の
body に書かれた内容をJavaで抽出したいだけなのだが、
わざわざ、それだけの処理で SAXParser インスタンスや DOM インスタンスをつくって重くなるのが嫌になってきた。
かといって、jsoup を使って読むほど難しい trタグとtdタグなわけではない。

正規表現と Stream での処理でなんとかしてみる。

public List<String> bodyListTD(String linestr){
   Pattern tdendPattern = Pattern.compile("</td>");
   Matcher matcher = tdendPattern.matcher(linestr);
   AtomicInteger ix = new AtomicInteger(0);
   AtomicReference<String> atomics = new AtomicReference<String>(linestr);
   while(matcher.find()){
      atomics.set(matcher.replaceFirst("</td_" +ix.getAndIncrement() + ">"));
      matcher = tdendPattern.matcher(atomics.get());
   }
   Pattern tdstartPattern = Pattern.compile("<td");
   matcher = tdstartPattern.matcher(atomics.get());
   ix = new AtomicInteger(0);
   while(matcher.find()){
      atomics.set(matcher.replaceFirst("<" +ix.getAndIncrement() + "_td"));
      matcher = tdstartPattern.matcher(atomics.get());
   }
   return IntStream.range(0, ix.get()).mapToObj(i->{
      Matcher m = Pattern.compile("<" + i + "_td.+</td_" + i + ">").matcher(atomics.get());
      m.find();
      return m.group().replaceFirst("</td_" + i + ">", "").replaceFirst("<" + i + "_td.*>", "");
   }).collect(Collectors.toList());
}

Pattern インスタンスを2回作ってタグ終了と開始のループ文が2つあって最後に IntStream で見つかったタグの数分の
抽出処理をしている。
→ダサいかも?

String linestr = "<tr><td>A</td><td>B</td><td class=\"column3\">C</td><td>D</td><td>E</td><td>F</td></tr>";

に対して、、

List<String> results = bodyListTD(linestr);

results.stream().forEach(e->{
    System.out.println("[" + e + "]");
}):

結果、

[A]
[B]
[C]
[D]
[E]
[F]

最終目的は、JavaScript ばかりに 表→JSON を作らせる処理を量が多いと処理時間が心配なので
いっそのこと、サーバに table の HTML を送ってしまって読み込ませた方が、むしろ速いのでは?
と思ったので、Java での table タグ解析の処理のためです。

表の編集

jQuery

前回の投稿、

入力フィールドでよく使いそうな jQuery 処理のメモ
http://oboe2uran.hatenablog.com/entry/2016/12/18/113206
と、
右クリックコンテキストメニューの基本サンプル
http://oboe2uran.hatenablog.com/entry/2016/12/11/161826


右クリックコンテキストメニューで追加の tableタグ表は、

右クリックコンテキストメニューの基本サンプルの 行追加、貼付け の処理に
入力フィールドでよく使いそうな jQuery 処理のメモで用意した関数を使用して

以下のようにして表の編集を書式スタイルに沿った入力に
することができる。しかも行挿入/削除/コピー/貼り付けに対応させる。

thead の th タグに data-type 属性で列のデータ属性を書いといて data-type に従ってセルをクリックした時の
input を各種の形式にするのである。

/******** click → input **************/
var initCell = function(){
   $('#target-table tbody tr').each(function(i, tr){ $(tr).children("td").unbind("click"); });
   $('#target-table tbody tr').each(function(i, tr){
      $(tr).children("td").click(function(){
         var n = parseInt($(tr).children("td").index(this), 10);
         n++;
         var datatype = $('#target-table thead tr th:nth-child(' + n + ')').data("type");
         console.log( datatype );
         $(this).html($("<input>").attr("type","text").attr("style", "margin-left: 1px; margin-right: 1px; width: 99%;").val($(this).html()));
         $(this).children("input").click(function(ev){
            ev.stopPropagation();
         });
         // datatype によって制御
         if (datatype=="code"){
            inputNumberForce($(this).children("input"), { 'numlock': function(){ callTooltip("#target-table tbody tr td input",  "NumLock OFF(消灯) になってます"); } });
         }else if(datatype=="date"){
            $(this).children("input").prop("readonly", "readonly");
            $(this).children("input").prop("style", "width:70px;background-color:#ffffff;");
            $(this).children("input").datepicker({ prevText:"前月", nextText:"翌月",
               changeMonth: true, changeYear: true, yearRange: '-3:+4',
               onSelect: function(datetxt, picker){ $(this).parent().html(datetxt); },
               onClose: function(datetxt, picker){ $(this).parent().html(datetxt); },
            });
         }else if(datatype=="price"){
            inputPrice($(this).children("input"), { 'numlock': function(){ callTooltip("#target-table tbody tr td input",  "NumLock OFF(消灯) になってます"); } });
            $(this).children("input").prop("style", "text-align:right;margin-left: 1px; margin-right: 1px; width: 99%;");
         }else if(datatype=="number"){
            inputInteger($(this).children("input"), { 'numlock': function(){ callTooltip("#target-table tbody tr td input",  "NumLock OFF(消灯) になってます"); } });
         }
         var targetcell = $(this);
         var inputer = $(this).children("input");
         $(inputer).focus(function(){
            $(this).select();
         }).bind("paste",function(){
            return false;
         }).keyup(function(eo){
            if (eo.keyCode==13 || eo.keyCode==9){
               $(this).trigger($.Event("blur"));
            }
         }).keydown(function(eo){
            // 229 で全角を全てcut
            if (eo.keyCode==229) return false;
         }).blur(function(){
            if(datatype != "date"){ // datepicker の場合はdatepicker で処理
               $(targetcell).html($(this).val());
            }
         });
         $(inputer).focus();
      });
   });
};



/******** 選択 ⇒ active ************/
var initListselector = function(){
   // 列数
   var columnLength = $('#target-table thead tr th').length;
   // コピー
   var copyTRhtml = null;
   // tbody contextmenu → 行挿入のみ
   $.contextMenu({
      selector: '#target-table tbody',
      build: function($trigger, e){
         return {
            callback: function(key, options){
               if (key=="add"){
                  $trigger.append("<tr></tr>");
                  for(var i=0;i < columnLength;i++) $trigger.children('tr:last').append("<td></td>");
                  initCell();
               }
            },
            items: {
               "add":{ name:"1行挿入", icon:"add", },
            },
            events: {
               show: function(){
                  console.log("-- contextmenu tbody show ---" );
               },
               hide: function(){
                  console.log("-- contextmenu tbody hide ---" );
               }
            }
         };
      },
   });
   // tr contextmenu → 行挿入/削除/コピー/貼付
   $.contextMenu({
      selector: '#target-table tbody tr',
      build: function($trigger, e){
         // コンテキスト行選択色→ ON
         $trigger.addClass("seleted-context");
         return {
            callback: function(key, options){
               if (key=="add"){
                  // 追加
                  $trigger.before("<tr></tr>");
                  for(var i=0;i < columnLength;i++) $trigger.prev().append("<td></td>");
                  initCell();
               }else if(key=="delete"){
                  // 削除
                  $trigger.remove();
               }else if(key=="copy"){
                  // コピー
                  copyTRhtml = "<tr>";
                  $trigger.children('td').each(function(ix, td){
                     copyTRhtml = copyTRhtml + "<td>" + $(td).html() + "</td>";
                  });
                  copyTRhtml = copyTRhtml + "</tr>";
               }else if(key=="paste"){
                  // 貼付け
                  if (copyTRhtml != null){
                     $trigger.before(copyTRhtml);
                     $trigger.remove();
                     copyTRhtml = null;
                     initCell();
                  }
               }
            },
            items:{
               "add":    { name:"1行挿入", icon:"add",    visible: true, },
               "delete": { name:"削除",     icon:"delete", visible: true, },
               "copy":   { name:"コピー",   icon: "copy",  visible: true, },
               "paste":  { name:"貼付け",   icon: "paste", visible: true, className: copyTRhtml==null ? 'contextmenu-item-disabled' : '' },
            },
            events: {
               show: function(){
                  console.log("-- contextmenu show ---" );
               },
               hide: function(){
                  console.log("-- contextmenu hide ---" );
                  // コンテキスト行選択色→ OFF
                  $trigger.removeClass("seleted-context");
               }
            }
         };
      }
   });
};

$(function(){
   initListselector();
   initCell();
});