<p>멀티테넌트를 사용하다보니 뷰를 써야할 것 같았는데,</p><p>테이블을 하나하나 수정하자니 시간이 너무 오래걸리는 것 같아서 자동화 하기로 했다.&nbsp;</p><p><br></p><p>근데 왠지 실서버에서는 사용하지 못할 것 같다. </p><p>뷰를 만드는데 생각보다 느린 것 같다.</p><p><br></p><p><br></p><p><br></p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div>DELIMITER&nbsp;$$</div><br><div><span style="color: #569cd6;">DROP</span>&nbsp;<span style="color: #569cd6;">PROCEDURE</span>&nbsp;<span style="color: #569cd6;">IF</span>&nbsp;<span style="color: #569cd6;">EXISTS</span>&nbsp;<span style="color: #ce9178;">`dsp_make_view`</span>$$</div><br><div><span style="color: #569cd6;">CREATE</span>&nbsp;<span style="color: #569cd6;">PROCEDURE</span>&nbsp;<span style="color: #ce9178;">`dsp_make_view`</span>()</div><div>&nbsp;&nbsp;&nbsp;&nbsp;COMMENT&nbsp;<span style="color: #ce9178;">'tenantId를&nbsp;사용한&nbsp;뷰&nbsp;만들기'</span></div><div><span style="color: #569cd6;">BEGIN</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;mCols&nbsp;<span style="color: #569cd6;">VARCHAR</span>(<span style="color: #b5cea8;">1024</span>);</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;mQuery&nbsp;<span style="color: #569cd6;">VARCHAR</span>(<span style="color: #b5cea8;">1024</span>);</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;mTableName&nbsp;<span style="color: #569cd6;">VARCHAR</span>(<span style="color: #b5cea8;">1024</span>);</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;mI&nbsp;<span style="color: #569cd6;">INT</span>&nbsp;DEFAULT&nbsp;<span style="color: #b5cea8;">0</span>;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DECLARE</span>&nbsp;mMaxI&nbsp;<span style="color: #569cd6;">INT</span>&nbsp;DEFAULT&nbsp;<span style="color: #b5cea8;">0</span>;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DROP</span>&nbsp;TEMPORARY&nbsp;<span style="color: #569cd6;">TABLE</span>&nbsp;<span style="color: #569cd6;">IF</span>&nbsp;<span style="color: #569cd6;">EXISTS</span>&nbsp;tmp_target;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">CREATE</span>&nbsp;TEMPORARY&nbsp;<span style="color: #569cd6;">TABLE</span>&nbsp;tmp_target</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #dcdcaa;">ROW_NUMBER</span>()&nbsp;<span style="color: #569cd6;">OVER</span>(<span style="color: #569cd6;">ORDER&nbsp;BY</span>&nbsp;t.TABLE_NAME)&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;NUM</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;t.TABLE_NAME</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.TABLES&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;t</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">JOIN</span>&nbsp;INFORMATION_SCHEMA.COLUMNS&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;C&nbsp;<span style="color: #569cd6;">ON</span>&nbsp;t.TABLE_NAME&nbsp;=&nbsp;c.TABLE_NAME</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;t.TABLE_SCHEMA&nbsp;=&nbsp;<span style="color: #569cd6;">DATABASE</span>()&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AND</span>&nbsp;t.TABLE_TYPE&nbsp;=&nbsp;<span style="color: #ce9178;">'BASE&nbsp;TABLE'</span>&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AND</span>&nbsp;c.COLUMN_NAME&nbsp;=&nbsp;<span style="color: #ce9178;">'CompanyId'</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">GROUP&nbsp;BY</span>&nbsp;t.TABLE_NAME;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;최대값&nbsp;설정</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;<span style="color: #dcdcaa;">MAX</span>(NUM)&nbsp;<span style="color: #569cd6;">INTO</span>&nbsp;mMaxI&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;tmp_target;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHILE</span>&nbsp;mI&nbsp;&lt;&nbsp;mMaxI&nbsp;DO</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;mI&nbsp;변수에&nbsp;값을&nbsp;증가</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SET</span>&nbsp;mI&nbsp;=&nbsp;mI&nbsp;+&nbsp;<span style="color: #b5cea8;">1</span>;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;여기서&nbsp;부터&nbsp;작업&nbsp;내역</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;TABLE_NAME&nbsp;<span style="color: #569cd6;">INTO</span>&nbsp;mTableName&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;tmp_target&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;num&nbsp;=&nbsp;mI;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;select&nbsp;cols</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;a.cols&nbsp;<span style="color: #569cd6;">INTO</span>&nbsp;mCols</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;GROUP_CONCAT(<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'`'</span>,&nbsp;COLUMN_NAME,&nbsp;<span style="color: #ce9178;">'`'</span>)&nbsp;<span style="color: #569cd6;">ORDER&nbsp;BY</span>&nbsp;ORDINAL_POSITION&nbsp;SEPARATOR&nbsp;<span style="color: #ce9178;">',&nbsp;'</span>)&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;cols</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;information_schema.COLUMNS</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;table_schema&nbsp;=&nbsp;<span style="color: #569cd6;">DATABASE</span>()</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AND</span>&nbsp;TABLE_NAME&nbsp;=&nbsp;mTableName</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#<span style="color: #569cd6;">AND</span>&nbsp;COLUMN_COMMENT&nbsp;<span style="color: #569cd6;">NOT</span>&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #ce9178;">'%서버만%'</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">GROUP&nbsp;BY</span>&nbsp;TABLE_NAME</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;a;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #6a9955;">--&nbsp;쿼리문&nbsp;생성</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #ce9178;">'CREATE&nbsp;OR&nbsp;REPLACE&nbsp;VIEW&nbsp;'</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'v'</span>,&nbsp;<span style="color: #dcdcaa;">SUBSTRING</span>(TABLE_NAME,&nbsp;<span style="color: #b5cea8;">2</span>))</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;<span style="color: #ce9178;">'&nbsp;as&nbsp;\r\nSELECT&nbsp;'</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;mCols</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;<span style="color: #ce9178;">'\r\nFROM&nbsp;'</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;TABLE_NAME</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;<span style="color: #ce9178;">'\r\nWHERE&nbsp;CompanyId&nbsp;=&nbsp;ufn_cm_tenant();'</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">INTO</span>&nbsp;mQuery</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.TABLES</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;table_schema&nbsp;=&nbsp;<span style="color: #569cd6;">DATABASE</span>()</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AND</span>&nbsp;TABLE_NAME&nbsp;=&nbsp;mTableName</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">GROUP&nbsp;BY</span>&nbsp;TABLE_NAME;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PREPARE&nbsp;stmt&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;mQuery;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">EXECUTE</span>&nbsp;stmt;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">DEALLOCATE</span>&nbsp;PREPARE&nbsp;stmt;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">END</span>&nbsp;<span style="color: #569cd6;">WHILE</span>;</div><br><br><div><span style="color: #569cd6;">END</span>$$</div><br><div>DELIMITER&nbsp;;</div></div><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p>

[MySQL&MariaDB] 전체 테이블에 tenantId를 사용하는 view 만들기